Articles

Aug 06 2015

Using the Vertipaq Engine in Power BI

Author: Ginger Grant • Discussion: No Comments

Recently I was at a client talking about Power BI. They had some questions about where to load data from their source system. After all you can just load data using the Data tab, and create a Pivot table based on that. Why use Power Pivot? The best reason that I could give is because if you load data up into Power Pivot, you are then going to use the Vertipaq, which is also called the XVelocity engine. The xVelocity engine loads data into memory and provides data compression which will increase the amount of data that you can store within Excel.

Demonstrating How the xVelocity Engine Works in Power BI

Since about 1890 Missouri has been known as the “Show Me State”. Apparently, no one really knows why, but I’ve heard the expression “I am from Missouri so show me”. I’m assuming that everyone reading this is from Missouri, so to speak, which
missourimeans I need to be able to show you how the xVelocity engine works. To do that, I am using a sample data set I got from UCI’s Machine Learning Archive, which is a great place for getting machine learning samples. The data set I selected for this test is the Online News Popularity set, which can be found here. If you don’t feel like downloading anything, do the same thing with any other large text file. In the Online News Popularity file there are 65 columns and

39,644 rows. The csv file size is 16,518 KB. If I open up that file and save it in the Excel file format and do nothing else, the file size is 18,484 KB. Saving the csv file in Excel adds about 2 MB to the file size. This isn’t surprising, as Excel adds to the text when it is saved, which can be seen if you ever open up an Excel file in Notepad.

The xVelocity File Size Test

So far we have not tested the xVelocity Engine yet. To test the xVelocity engine, open up Excel, click on the Power Pivot tab, select Manage, which will open up Power Pivot. Within Power Pivot, click on the From Other Sources icon, and select Text File, then click on the Next button. Browse to the Online News Popularity.csv location, wait until the data loads, then click on the Finish button. The data will then be loaded into Power Pivot. Save everything and exit Excel. The file size for the Excel file with the data loaded into Power Pivot is 11,386 KB, which is a 39% reduction in space. In playing around with various files, I have noticed that you get the biggest reduction if you have few columns and many rows. This works the same way in Power BI. Now I know that the xVelocity Engine does more than just shrink files, but showing someone the decreased amount of memory usage just doesn’t have the same impact as file size. This is a simple experiment which shows the improvements made when using Power Pivot for Excel or Power BI, which I hope you find useful as well.

Yours Always

Ginger Grant

Data aficionado et SQL Raconteur

Moving from Power BI to Analysis Services Tabular

Update By: Ginger Grant
Date: August 2, 2015

Power BI is a great tool for creating data mashups and great visualizations. There are a lot of posts on how and why to use Power BI for these tasks, including mine. PowerBIDesktopModelAfter working with a number of clients on how to make implement Power BI in their data environments, I thought it might be helpful to talk about how Power BI works internally to provide guidance to determine how best to implement it. One of Power BI’s big strengths is the ability to bring data in from a lot of different sources and put them together into a single data model. Power Pivot in Excel and Power BI Desktop in the Relationship’s page both allow you to create data models. Power BI on the web, does not, as of this writing allow the ability to create data models from multiple sources. Power BI is able to handle a large amount of data to be stored within it using the Vertipaq engine which compresses the data and loads it in memory. How much data? Well that has to do with how much memory you have.

Having 8 GB of Memory on a 32 bit Operating System is Worthless

Recently I was working at a client who wanted to load five year’s worth of operating information into Power BI. He knew that Power BI needs a lot of RAM, so he requisitioned 8 GB of RAM for his laptop so he would be able to load this much data. However, his IT department had a policy of only installing 32 bit operating systems on the computers. When 64 bit operating systems first became available, often times the computer bios or the mother board wouldn’t support it. Until very recently, outside of developers, most people didn’t have a business reason for 64 bit operating system to do their job.

Power BI will use all the memory you have available. What is available? Here’s some math to explain how much memory is available. A 32 bit Windows operating system can assess 2 32 = 4GB of RAM, and you don’t even get all of that as the system uses some of it. This math applies if a 32 bit version of Office 2013 is installed onto a 64 bit version of the operating system. The client couldn’t load five years of operational data into Excel because he didn’t have enough addressable memory, since with a 32 bit operating system he had 4 GB of RAM he can use, period.

Reasons for Data Modeling in Excel Power Pivot

Power BI is a great tool, but it is not the only tool. If you want to load up years of data into your model, at some point, even if you have a 64 bit OS and 32 GB of RAM, eventually you will run out of memory. Well then what do you do?ImportPowerPivot If you have developed your data model in Excel, no problem you can upgrade it to Analysis Services Tabular. Visual studio has a tool to make migrating to Analysis Services Tabular easy to do. I find Power Pivot model to be one of the best ways to get started on creating a Tabular Model as you can test out your data modeling concepts very quickly. If you have developed your data model in Power BI Desktop, as of this writing there is no way to migrate directly to Analysis Services Tabular. If you are familiar with Power BI, you may be pleasantly surprised at how similar Analysis Service Tabular is. Along with improved performance accessing the data, there are a number of security features which are not available in Power BI in Analysis Services Tabular. With Power BI, you can create a corporate solution from a desktop application, which is one of the neatest things about Power BI.

 

Yours Always

Ginger Grant

Data aficionado et SQL Raconteur

 

 

 

 

Jul 26 2015

Why Power BI Desktop and Not Excel Power Pivot?

Author: Ginger Grant • Discussion: 5 Comments

As regular readers of my blog know, I spend a time working with Power BI. Since it was released Friday, July 24, 2015, it started me thinking about all of the people who have been spending time on the previous version which, I affectionately thought of as the 4 Powers in Excel plus web. Well since the Office 365 web version is going away on December 31, 2015, I thought of the many ways you could use the new Power BI with the Excel.

Power BI on the Web is a Full Featured Application

One thing to keep in mind is that the web component of Power BI is very powerful. If you are not interested in mashing data so much as reporting on data from one source, be that SQL Server or Sales Force, select the needed data into the Dataset section of Power BI, and start working on creating visualization reports and pinning sections of those reports to a dashboard. Please note, if you create a visualization on the web there is not currently a way to download it to the desktop, meaning reports created on the web, stay on the web and you cannot download them. There is no reason to use Power BI Desktop at all. That being said, the web version is a tool not the tool, so there are times when you may need to work with either Power BI Desktop or Excel and load those models into Power BI on the web.

When do I want to use Excel for my Data Modeling and Not Power BI?

If you have created a working data mashup in Excel Power Pivot, it makes sense to load that to the web directly and use it as is. It is not possible to bring in an Excel Power Pivot model into Power Pivot Desktop, because it doesn’t read the data in Power Pivot as a data source. If you have spreadsheets containing data it will read those in, but if you have a Power Pivot model which doesn’t use any linked tables, it won’t find the data. However, if the same excel spreadsheet is loaded to the Web Version of Power BI, the data model created will be found. If you have a model created, select Add Data in the Web version to add the model and use it to generate visualizations. Power View does not have all of the modeling types which Power BI now has, so if Tree Maps or Funnel charts should be displayed either web or the desktop version of Power BI will do it. If you need to update your model in Excel, make sure that you store the model within One Drive so that the Power Pivot updates you create will be automatically migrated to the web.

When to use Power BI Desktop?

If you have anything but Excel 2013, use Power BI Desktop. Power BI Desktop provides visualizations PowerBIDesktopvery similar to Power View, which are not available outside of Excel 2013. If you would like to create a new data mashup, start by using Power BI Desktop, because it has some new modeling features which you might want to take advantage of such as Many-to-Many and Cross Filter Direction. It handles role playing dimensions the exact same way that Excel Power Pivot does, meaning you can only have one active relationship at a time. The features you need for data modeling are all their, just moved around a bit, such as using DAX to create columns or measures. Power BI Desktops allows you to create data mashups from multiple data sources as well, allowing data to be modeled from as many sources as the model will tie together.

Favorite Power BI Features

After using the previous version of Power BI, I figured I would list the features I most like in the new version, which are other reasons I would give for switching to it from Excel.

  • Colors – Getting to pick whatever color I want for my charts is wonderful. I was so getting tired of blue.
  • Data Refresh – Getting the automatic Data Refresh to work in Office 365 was complicated. The new application for refreshing Personal Gateway is wonderful as it is so easy to use. The only thing I don’t like about it is the name as I don’t see why creating a connection to a server be Personal?
  • New Visualizations – Options are great and I love having more to select from.
  • Lack of dependency on Excel Versions – It can take a while, especially in large companies to get everyone upgraded to the latest version of Excel. Now that process doesn’t keep good visualizations from happening.
  • Price – At $9.99 a month or free if you don’t mind manually refreshing your data and don’t have much data, the cost decrease helps win the argument to move to it.
  • Separation from SharePoint – the underlying administration on the web version with SharePoint in Office 365 added a layer of complexity which thankfully has been removed.

I have a lot of other thoughts on Power BI, but this is getting pretty long, so I will save my thoughts for another posting. Since it doesn’t cost anything to get started, I highly encourage everyone to start playing around with Power BI and let me know what you think.

 

Yours Always

Ginger Grant

Data aficionado et SQL Raconteur

Jul 09 2015

Datazen – The On-site BI Option

Author: Ginger Grant • Discussion: 2 Comments

I have talked to a number of people who looked at Power BI Preview and read that it will be replacing Power BI Office 365 as a cloud solution for mobile self-service Business Intelligence [BI] and thought what are we going to do? Some people are not going to go to the cloud. Since they have made that business decision, they were wondering what Microsoft was planning on offering in the non-cloud space? I was curious too, so I asked my local Microsoft Technical Sales Representative about what was the plan for people who wanted on-premises BI. The answer to that question is, use Datazen. Datazen is positioned to be the application for people who don’t want cloud but do want to create a rich self-service environment for doing self-service BI, which of course means that you can send the reports to tablets and phones.

Datazen vs Power BI Preview

When comparing the two products Datazen and Power BI Preview, there are a number of features which will sway people one direction or another.DataZenVisualizations  For example, Datazen doesn’t integrate with PowerPivot models, you have to flatten the model to connect. On the plus side, Datazen has a very robust security and distribution model which Power BI Preview does not. There are more visualization types in Datazen and the snap to grid makes using them a breeze. Power BI has the ability to connect with QuickBooks and other data sources such as Salesforce, which Datazen cannot do. Datazen is included with a current SQL Server maintenance agreement. Power BI will have an on-going per user monthly fee. Both have the ability to allow users to pick their own colors, rather than limit the selection to a series of color pallets, and both deploy to tablets and phones in addition to a web page. All of these factors, plus a whole lot more than I have space for here, weigh into the decision of which product you may select. If the foremost criteria for mobile Self-Service BI is No cloud, Microsoft’s solution in the self-service BI space is, use Datazen.

Future is Cloudy

Recently most of Microsoft’s new products have been very cloud based. Azure Machine Learning, is a great example of this, as no where can you find any document on Microsoft’s site about any plans to move that out of the cloud. The purchase of Datazen shows Microsoft is still accommodating people who just want to stay in house, which I missed in their announcement. I’ll be reading the upcoming releases a little more carefully from now on, as I am sure there will be many more.

Yours Always

Ginger Grant

Data aficionado et SQL Raconteur

 

Jun 12 2015

Configuring Power BI Data Refresh on a Local SQL Server Database

Author: Ginger Grant • Discussion: 5 Comments

Recently I have been tasked with setting up and assisting other in setting up Office 365 Power BI Automatic Data Refresh. If you scroll through this post, you’ll see that there are detailed steps required to accomplish a successful Power BI Data Refresh with an on site version of SQL Server. There are a lot of people who are very frustrated trying to configure Automated Data Refresh. Considering what is involved, I understand why. Follow these steps, and Automated Data Refresh for Power BI Office 365 with a SQL Server located locally will work.

TL;DR Steps for Power BI Automated Data Refresh

This is a long document because I wanted to make sure that all confusion regarding how to make this work took a lot of words. If you don’t want to read them all, here are the steps needed to complete a successful data refresh. I have a feeling this level of detail may not be enough, some people may figure it out after reading this far.

  • Install the Data Management Gateway application on your SQL Server box.
  • Add a gateway Power BI Admin Center and configure the Data Management Gateway.
  • Create a connection string and a Power BI workbook which uses the connection string
  • Create a Data Source in Power BI Admin Center using the connection string created in the previous step
  • Schedule Data Refresh using the Power BI Workbook using a matching connection string.

 

Power BI Office 365 Automated Data Refresh Steps

Here in detail is how I can get Automated Data Refresh to work every time. If you have figured out another way to make it work not using these steps for an on-premise SQL Server data refresh, please let me know how you did it. I know these steps work every time, but I am hoping that there is an easier way.

  1. Install the Microsoft Data Management Gateway on your SQL Server Database Server. Here is more information on the Data Management Gateway.Technically, you don’t have to have it installed here as Microsoft says it is supported installed in other places. If you want to make sure it does work, you will install it on your database server, which must have internet access available on the server. Here is the link for the software, and here are the instructions from Microsoft on installing it.

 

  1. The next step is to configure the gateway for the Power BI site and for your local server. Go to your Power BI for Office 365 site, and navigate to the Power BI Admin Center. On the Left Menu, you will see an option for Gateway. Click on the plus and add a gateway.  You will generate a key as part of this process and you will need to copy this key and provide the key to the application installed on the database server. If you have more than one database server, you will need more than one key. Here’s a link to the instructions for creating a gateway in the Power BI Admin Center which provide more detail. When you are done, look on Power BI Admin screen your gateway name will have this Running next to the gateway name. If you don’t see the green check, wait for about 10 minutes to see if the status changes.If the status does not show as Running, there are two things you might want to do: Review the instructions or Look on the Office 365 Admin Center. The Office 365 Admin Center can be accessed via the grid button on the top left hand side of the screen under the Admin icon. Select menu option on the left hand side of the screen for Service Health, open it and select service health. I have included a copy of the screen here, which shows that there are no issues today. If you are experiencing issues, this screen may not reflect a problem until sometime later, so continue to review this if the steps just don’t work for some reason.Office365Admin
    1. Create a local data connection within Excel to be used to create a Power BI data source. Start a new worksheet in Excel. Click on the data tab (not the Power Pivot tab) and click on the left most icon on the ribbon Get External Data->From Other Sources->From SQL Server. Enter the server name. Either Windows Authentication or SQL Server Authentication work, so pick either one and enter the Id and password. Click on the Next Select the database and then the tables you want. There is no need to select more than one table, as you can always go back and add tables once this is complete, and it will save loading time. Click on the Next button.
      DataConnectionWizardThis is the most important screen of the entire process. The Friendly Name listed here will be used on the Power BI site to create a Data Source. The Friendly Name must exactly match the name of the Data Source in Power BI, which may mean that you will want to edit the name from the default listed here. Do not check always attempt to use this file to refresh data. After you are satisfied with the Friendly Name click on the Browse… button.In a later step, the connection string listed in this file will be used to create the connection string in Power BI. As we will be opening the file listed here in Notepad in a later step, it is important to remember this file path.DataConnectionWizardFileSaveClick on the Save button in the File Save window.Click on the Finish button in the Data Connection Wizard Menu.ImportDataGenerally speaking, you will want your database information to be loaded to a Pivot Table Report, so click on the OK button. If you do this, you have the added benefit of having a report to test. When your pivot table is completed, it might be a good idea to now go to the Power Pivot tab and take a look at the model. Feel free to modify it as much as you like, but it is not necessary to do so to test the data refresh. If you need to add additional tables, make sure that you do so by using the existing data connection created earlier. Save and close your new excel workbook.

     

    1. Using the information from the previous step, we can now create a data source in Power BI. Go back to Power BI Admin Center on the Web, and from the left hand menu select Data Sources. On that screen, click on the +(plus sign) to add a new data source, and select SQL Server. Enable Cloud Access must be completed. If you want to make items from the database searchable from Power Query, you will need to check Searchable in Power Query and Enable Odata Feed. If you select Enable Odata Feed you will be prompted later to select a series of tables to expose for viewing Click next to get to the connection info screen.PowerBIConnectionInfoCompleting the connection info screen correctly will allow you to do a data refresh. The information needed to complete the screen can be found in the *.odc file you created earlier. In notepad, open up the *.odc file using path you remembered from the previous step. Look for the text <meta name=Catalog content= The name listed on the other side of the equal sign MUST be the name you list in the name in the Power BI Connection Info Name. Put a description in if you wish. The dropdown box for the Gateway will list the gateway you created in the previous step as well as any others you have created previously. Select the appropriate gateway.By default the Connection Properties option will be selected.ConnectUsing Unfortunately if you use this option you will never be able to perform an Automatic Data Refresh in Power BI. Select connection String. When you do, the connection provider will change to .NET Framework Data Provider OLE DB. This is supposed to happen. For the connection String information, go back to your *.odc file and look for the text <odc:ConnectionString> Copy everything between that text and this </odc:ConnectionString> into the connection string window. Wait until the set credentials button turns blue. If it does not turn blue, there is something wrong with the Power BI and you will want to look at the Office 365 Admin Service Health site referenced earlier.Click on the Set Credentials button. A little window will show up indicating it is loading. A pop up window will appear prompting you to add your user id and password. Make sure you test the connection before clicking on the OK button.The last step here is to click the Save button.

     

    1. Upon the successful completion of the previous steps, Scheduling Data Refresh can now be configured. Upload the excel workbook created earlier in step 3. Once it is loaded, click on … (the ellipse) and select Scheduled Data Refresh. When the AddToFeaturedReportsscreen loads, the first thing you will need to do is click on the button at the top of the screen to turn the feature on as it defaults to off. All of the connection information included inside your Power BI report will be listed. The connection name(s) listed in the report should exactly match the connection(s) in the Power BI Admin Data Connections. If there is not a match, you will not be able to Schedule an automatic data refresh. It is not possible to change the names from this screen, as it lists the connections within the Power BI document. You may need to fix the connections within your Power BI worksheet. If the connections are not valid, you will see this status iconStatusIcon and a message about this status.
      The Configure Refresh Schedule is default selected to a daily frequency.   Select the time and the zone for the data refresh. Unfortunately, you can only schedule refreshes for 3 months, so the schedule will need to be updated every 3 months as there is no way to make it continue in perpetuity. Send Notifications is defaulted to the email address of the admin. Note you will be notified of errors, not of completions of the data refresh. Click on the Save and Refresh button to test the data refresh process, which refreshes the report immediately. Once the data refresh schedule is completed, the top of the screen will have two menu items History and Settings, which you can review at any time.

     

    Data Refresh Conclusion

    If you think that there should be a better way, I would love to hear about it. Looking online, there are a lot of people struggling with this topic. If in any way you find it helpful, or you want to tell me there is an easier way, I would love to hear about it. I look forward to hearing what you have to say about this topic.

    Yours Always

    Ginger Grant

    Data aficionado et SQL Raconteur

Apr 14 2015

Musing about Microsoft’s Acquisition of Datazen and Power BI

Author: Ginger Grant • Discussion: 3 Comments

DataZenMicrosoft just announced that they have bought Datazen, a mobile data visualization product. While I have no idea what Microsoft is actually going to do with the Datazen product, I couldn’t resist the chance to speculate about it. In earlier posts, I’ve talked about the conversion of what Power BI was before Power BI Designer was released and what Power BI is now. Since then I have been working on creating new Power BI dashboards. The process left me, shall we say underwhelmed? The tools in Excel allow for much greater flexibility and options than new Power BI. Now to be fair, new Power BI was released December 18th, 2014, so it’s not possible for it to contain all of the rich feature and functionality that the Excel tools do. That’s all well and good, but what it won’t do led to some frustration. If the new Power BI was the way that Microsoft was going to climb up to the top of the Gartner BI visualization charts, I didn’t think it was going to do the trick.

Anyone Still Using Lotus 123?

The one thing that I kept on thinking about when looking at the new Power BI is, there has to be a part of the plan I’m not getting. I didn’t see how this product would have the feature and functionality needed by the time the reviews came around again next February. In looking back in time, I couldn’t help of thinking of a time when Microsoft was battling it out in another space, spreadsheets. When Excel first came out, the big leader in the space was Lotus 123, which has since disappeared. (If you are running it where you work, please post comment to let me know, because I think Lotus 123 is gone.) The reason for Microsoft’s dominance in spreadsheets was Excel got a lot better at providing spreadsheets the way people wanted to use them.

Datazen, Hopefully Not the Next ProClarity

Microsoft’s purchase of Datazen looks to be a way to leverage a product with some really cool features to enhance the capabilities of Power BI. Datazen is a mobile application, but they have some good looking visualizations which hopefully could be incorporated into Power BI. There’s only one thing that may be a reason for pause. In 2006, Microsoft made another acquisition. They bought a company called ProClarity. ProClarity had some really neat features, some of which were included in Performance Point, but for the most part, the application was killed. I hope that history is not a guide in the purchase of Datazen, because Datazen has some great visualizations which could really help the new Power BI, and it would be good if Microsoft could figure out how to merge the features into the new Power BI to help improve the their position in the data visualization marketplace. I look forward to seeing how the two companies merge the Datazen features into Microsoft’s data visualization components.

Yours Always

Ginger Grant

Data aficionado et SQL Raconteur

Feb 10 2015

Licensing of Power BI moves away from Office 365

Author: Ginger Grant • Discussion: No Comments

The gap between Power BI and Excel keeps on getting wider. As there is conflicting information about the Excel/Power BI break-up on various places on the internet, I wanted to clarify some of the common discussion questions.  One place where you can get definitive answers is in the new Licensing Information for Power BI which I linked in case you missed it. Unfortunately for those of us who have been paying the higher fees for Power BI, the price reduction to ten dollars isn’t immediate. Although I am disappointed, the non-immediate fee reduction makes sense, since the new-Excel-free-version of Power BI is still preview edition. No one outside the US is able to even try it yet. The new pricing will be available when the new product is available. This also gives people a chance to migrate their existing reports to the new version of Power BI. Another way of saying this is, going forward you don’t need Excel or the Four Powers – Power Query, Power Map, Power Pivot and Power View – anymore. The only thing you’ll need is the New Power BI.

No SharePoint needed for Power BI

Another thing that the licensing document makes clear, is when the new Power BI is released, the Office 365 version of SharePoint will not be required. To be even clearer, SharePoint will not be needed to use Power BI. There are several places online where I have read conflicting information regarding the need to have SharePoint. Let me clarify by quoting from Microsoft’s Licensing Information for Power BI page just to make it perfectly clear “Power BI service will become a standalone service and will no longer require SharePoint Online”. Since the current version of Power BI is using SharePoint, if this is the only reason you have Office 365 SharePoint, you can get rid of SharePoint, which will be an additional cost savings. How much will it cost? Talk to Microsoft Support as the details must be worked out with them.

Why Did Microsoft Change Power BI to not use Excel ?

While at SQL Saturday in Albuquerque, which was even better than my high expectations, I had a chance to talk to someone from Microsoft, and of course Power BI came up. I asked why he thought Microsoft moved away from Power BI? While not divulging anything that is covered under an NDA, he mentioned that there were a lot of people who would like to use the features of Power BI, but they didn’t have the right version of Excel within their organizations. Microsoft removed this barrier to adoption by moving to a non-Excel version. Excel also had a lot of features that weren’t needed for data visualization, and support for some of the current features was sort of confusing. I agreed with him. For example there are three different ways of creating a data connection, which is definitely confusing.

Scheduled Updates in Excel

For those people who like the Power BI Add-ins to Excel and want to stay with them, there is one big issue, scheduled updates. Only with Power BI deployed to the Office 365 Cloud SharePoint can you get scheduled updates from all the places which you might be retrieving data. For all those people who for a variety of issues didn’t want to go with Power BI deployed that way, there is now a solution. If you are looking to update Excel, Power Update is what you need. Kudos to Rob Collie for providing this solution as I have heard from a number of people that they had SharePoint and didn’t want to go to the cloud for Power Query updates or didn’t want to have to deploy SharePoint. Keeping up with Excel and Power BI is now a wider world than just Microsoft.  The one thing you can count on is things will always be changing.

 

Yours Always

Ginger Grant

Data aficionado et SQL Raconteur

Jan 28 2015

What is Power BI? Microsoft just changed the answer to the question

Author: Ginger Grant • Discussion: 3 Comments

In reading the Power BI blog, it appears Microsoft has just changed what Power BI is. Since Power BI’s introduction last year, Power BI was a series of add-ins to Excel, which I liked to call the four powers, Power Pivot, Power Query, Power View and Power Map, and a web component. For people who weren’t interested in the web component, the most of the features listed in Power BI are available to anyone who has Office 2013 or Office 365. That now appears to have changed. Microsoft now says that Power BI is a Cloud Based service, and doesn’t mention Excel at all.

New Power BI

As I documented here, Microsoft released Power BI Designer as a web application and a stand alone application. It has been freely available since December 18, 2014 to anyone living in the US. This was the first step away from Excel as you no longer needed Excel to create dashboards. In the January 27 announcement, Microsoft has completely broken away from Excel. Now Power BI is new, and the new Power BI is the Designer of December. The old restrictions still apply. Sorry, if you are not in the US, you can’t use Power BI Designer aka the new Power BI. You can get an iPad version of the app in the Apple store and the Surface version in the Microsoft store, but phone support is not yet available for either iPhone or Android, but they are slated for release later this year.

New Product, New Price

It appears the mall is not the only place having sales in January. Microsoft just announced a major price reduction in Power BI too. Here’s the previous pricing model, which I saved from Microsoft’s website, just in case the Power BI webpage changed, which it did.

PowerBILicensing

Here’s the new pricing model ,which doesn’t fit very well on my webpage.  To save you having to click on it, I will cut to the chase. Power BI is now $9.99.   Now that they product is targeted to the masses, the price isn’t an even number, just like everything Not Sold In Stores. It stands to reason that dropping the price will help in the  wider adoption of Power BI. The previous pricing made Power BI much more expensive than Office 365, which was probably a tough sell to many IT managers.

What’s Next with Power BI ?

To be honest, I have no idea what Microsoft is planning next. This announcement marks a big break with the past, which I guess we could call Power BI 1.0. The new direction to a standalone and web product makes Microsoft look more like its competitors, which I am sure was the idea. Personally I thought the break with Excel was quite surprising as I thought the plan was to leverage the knowledge of the current user base, so I didn’t expect it. I wonder if they are going to rebrand the four powers in Excel?  Based on today’s announcement I wouldn’t be surprised, and I will be watching Microsoft closely to see what happens next.

Yours Always

Ginger Grant

Data aficionado et SQL Raconteur

 

Jan 17 2015

Running the local version of Power BI Designer Preview

Author: Ginger Grant • Discussion: No Comments

Power BI Designer is available both as a web application and as an desktop application which you can download to your PC. As you might have surmised, the two versions are mirrors to one another when it comes to functionality, with the biggest difference is the need to upload the results. The local version allows people who have an older version of Excel loaded to do Power View-like report functionality which they may not be able to do with the version of Excel they have.

Getting Started

If you want to get started with Power BI Preview and you live in the United States, click here www.powerbi.com/dashboards . You don’t need to be a current Power BI customer to download and use the preview. The best way to learn how to use it is through the videos which Microsoft has included on the application start up screen shown below. I highly recommend watching them as they succinctly describe how it works.PowerBIDesignerVidoes

Things to keep in Mind when using with Power BI Designer

This product is still in preview, so there are some things that you can’t do, like change the colors. It was also meant to be non-developer friendly, so Power BI Designer picks a lot of things for you, and then you can change them afterwards. This model may be a little disconcerting if you don’t care about line charts, which seem to be the default. Microsoft created this program with the intent that you would be uploading the finished product to a Power BI tenant, it isn’t designed to have any native security or data refresh. This is not Excel, as the files you save in Power BI Designer have the suffix PBIX, so you have to have Power BI Designer to open these files.

Generating Dashboards

The steps for creating a dashboard are identical to how the Power BI Designer works with the online preview. First you need to select a data set, which can be from nearly anywhere–Azure, HDFS, Facebook, SSAS, Sql Server, MySQL,Sales Force, csv, to name a few—and then it will attempt to visualize your data sets for you. If you don’t like the visualization, most likely a line chart, which was defualt selected you can select a different visualization format, such as a treemap, funnel chart or any of the different options. To complete the dashboard, merge your selections onto one screen by tiling smaller visualizations onto a dashboard. The data displayed on the dashboard is contextual, so if you change the value all of the corresponding visualizations will change along with the selection.

Wait, There’s More

Microsoft is just now rolling out with Power BI Designer, and like Ronco’s ginsu knives, there are a lot more things planned for the product in the future. Here’s a list of suggestions made so far. If you think the product really needs something, go tell Microsoft as it appears they are actively monitoring the feedback. When they do release something I find noteworthy, I’ll let you know in a later post.

Yours Always

Ginger Grant

Data aficionado et SQL Raconteur

 

Jan 07 2015

The Changing Forecast of Power BI

Author: Ginger Grant • Discussion: No Comments

On Thursday, I have the opportunity to talk about parts of Power BI which reside outside of the Excel. I’ll be showing the some of the web components of Power BI and how to use them. Because the install base of the Power BI tenant is not nearly as high as the number of people who have used the Power BI components within Excel, this webinar will highlight the more exotic features. When I signed up to do this webinar, I planned to showcase forecasting and hindcasting as they are neat ways of visually showing predictive data analytics. Having used them before, I thought that demoing their capabilities once again would be a good way to show some of the neat reasons that you might want to use Power BI. Unfortunately, I won’t be able to demo these features.

Forecast Maintenance

Imagine my surprise when I saw this announcement from Microsoft stating that the Power BI Forecasting feature was “offline for maintenance”. Since the announcement came in December, I originally thought that this would be no big deal. Surely the feature would be back online soon. I thought wrong. The forecasting feature is gone. When using Power BI, the option never appears. It’s like it never existed.   In the excitement to release Designer, the non-excel dash boarding feature, the disappearance of the previously released tool has not been highlighted, until now.

The Dark Cloud of Iterative Releases

When the Forecasting feature was released in May, there was a lot of buzz about the fact that Microsoft was able to release a great new feature in time to coincide with the PASS BA Conference. Well, the pulling of this feature is the Nimbostratus of the cloud release, bringing rain. There are a lot of features available for me to show in Power BI, so I still have a lot of demo material, but I find this development to be rather troubling. Here’s hoping that this giving and taking of features in Power BI doesn’t repeat itself.

Yours Always

Ginger Grant

Data aficionado et SQL Raconteur