Articles

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

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 11 2015

Presentation Follow Up to Data Analytics and Distribution with Power BI

Author: Ginger Grant • Discussion: No Comments

Thank you to all of the people who took the time to view my session on Data Analytics and Distribution with Power BI. I really enjoy getting the chance to decrease the confusion I hear regarding Power BI, and hope that you will find the question and answer section helpful if you are trying to learn more about the product.

Questions and Answers

 In Power BI Designer, is it possible to manipulate the colors of the charts?

While this may change as Power BI Designer is still in preview mode, currently the colors are assigned automatically. As you might guess, this is a feature that other people are interested in so it is on Microsoft’s list of things to add. If Power BI Designer color selections are implemented like they are in Power View, which is very similar to Power BI Designer, it is likely themes of colors will be available, rather than the ability to pick each color like in Report Designer, but we will have to wait and see.

Are Power BI designer/dashboard changes specific to each user?

If you have created a Power BI Designer Dashboard, you have the ability to share it with people in your organization. There are a couple of things that need to happen for this to work. The people that you share it with must have Power BI accounts, and they have to be in the same domain as you are. When you share the reports they are only able to read, not edit them. For more information regarding security and Power BI, see Microsoft’s guide here.

Can I grant access to users outside of our domain?

Power BI’s security model is a separate tenant from the security model for SharePoint in the Office365 cloud, but they are related as you can only grant access to Power BI if those users are able to access your version of Office 365 SharePoint. As stated in the previous question, for Power BI Designer Dashboard, the users must be part of the same domain.

How does Power BI perform predictions? Is it the same logic which is used in Data mining?

Power BI uses the Forecasting and Hindcasting features to perform predictive analytics. There are a number of different analytical categories, and the kind used in Power BI use Time Series. As the name suggests, Time Series models analyze a set of measurements performed over time to determine patterns in the past which can be used as guides going forward. Data Mining looks at variables, which may or may not include time, as it looks for patterns throughout the data. These underlying statistical models are not the same.

Does Power BI have Power Map feature?

Power BI definitely contains Power Map. In fact Power Map is only supported in preview mode if you do not have Power BI. This link can provide more information about the limits of Power Map in Excel. Power Map is designed to be run as a movie, and provide a directed look of the data on the map, rather than providing the interactive drill down mapping features which are available in Power View. You can share a Power Map by saving it as an mp4 video file and posting it anywhere. There are a number of Power Maps on You tube if you care to search there.

How does “R” play here?

The R language is completely agnostic as to what is the source of its data, you have the ability to use excel if you want to as a data source. If you want to use R within Excel, try the RExcel add-in, which is available here.

Is the PowerPivot where the data is stored for Power BI Designer?

No. Data can come from anywhere, not just from Power Pivot in Excel. For example, if you want to use a website as a data source, you could do that too as there are a number of different available data sources, and that is one of many.

Do you have to use Power BI Designer on the Web?

While Microsoft has designed the Power BI Designer as a web project, so that you can create Power BI Designer Dashboards as part of the preview, there is also an application available for download here. The desktop application works very similarly to the web version, with the exception of course that you will need to upload and Select Power BI Designer file as your data source. Should you wish to modify the dashboard once it is loaded, you can do so.

How would you determine anomalies or freak instances in data versus true trends?

The problem of determining anomalies is one which the practitioners of predictive analytics are constantly struggling. For trending to occur, the numbers of what was previously considered an anomaly need to increase. Forecasting within Power BI applies one of the more standard methods for accounting for anomalies, looking at the standard deviations and probability. The likelihood a number will fall within a certain range of numbers are based upon the number of times this has happened in the past, which is graphed as a bell curve. The values representing the far sides of the Bell curve are discarded, which is known as variance, which in Forecasting in Power BI is represented as 1δ .The number increases with the more variance you wish to represent.

Does SharePoint on premise support Power BI Designer?

No. Power BI Designer is currently in preview version in the US for Power BI subscribers. You can download the application to play with it.

Will Power BI be available in next version of SharePoint?

While I cannot speak for Microsoft, I can tell you that it isn’t there now. For more information on SharePoint, check out their website here.

Does this work in Office 365 SharePoint? And this replace the bi feature offered in SharePoint on the cloud?

Since I do not work for Microsoft, I am hesitant to talk much about how their licensing plans really work. For more information, please check out their website.

If we want to start learning Power BI, where do we start?

There are a number of great places to learn about Power BI, the best and most up to date being here. I have included some other places where you might want to go to learn more about Power BI

 

Yours Always

Ginger Grant

Data aficionado et SQL Raconteur

Dec 26 2014

Creating Couture Dashboards with Power BI Designer

Author: Ginger Grant • Discussion: No Comments

On December 18, 2014 Microsoft release the preview of PowerBI Dashboards, as long as you live in the US. Since Power BI has an iterative deployment cycle, releases have been rapid and varying in detail.  This release is huge as takes a further step from separating Power BI from Excel.  With this release, creating data visualizations moves to a new tool, the Power BI Designer.  When this preview is fully released so countries outside of the US have the ability to play with it, Excel is no longer necessary. Of course the option to use Excel is still there, but creating visualizations, be they dashboards or not, can be done on the web with Power BI Designer.

Dashboards, the New Old feature

As anyone who has reviewed the marketing information from Microsoft knows, dashboards are not new in Power BI.  The ability to put multiple data representations on one report has been available since the product was first previewed.  The difference is Excel is no longer required.  Since the Power BI was released, it appeared to be just an expansion of Excel’s feature set.  The one power tool which was supposed to be only available to Power BI subscribers, Power Map, was released to existing Office 365 users after the preview version expired. It’s not exactly the same version of  PowerMap, as only Power BI users get updates but Excel users can still use the preview version. Power Query, Power Pivot and Power View have been included in Excel’s features for a while, which left many users thinking there was no reason to pay for Power BI, unless you wanted to distribute data visualizations in a Mobile Environment.  Being able to design reports completely in the cloud provides a more compelling reason to sign up for Power BI.

DIY – Implementing Dashboards on an Existing Power BI Account

The new dashboarding feature is still in preview, so I assume that a lot of the access methods for it are going to change, so keep in mind that these instructions may not work in the next release, which could be soon. Here’s what you see when logged into your PowerBI account.

PowerBIDashboard

The first time you click the try it now link, Microsoft will update your account, which means that you will need to wait until it loads to use it.  After they are done, this link will work to load the Power BI Dashboards.  Keep in mind since this is a US only preview, the new dashboards are not fully integrated into PowerBI, as the visualizations there don’t show up in your favorites, but it is logical to assume that it is coming.  Clicking on the Try It Now button brings up this screen.

PreviewDashboards

Designing Dashboards is meant to be simple, so the steps involved to create them are few.  First you need to select a dataset, on my screen you will see that I have picked an Excel spreadsheet called TabularReports.  Then you select the data that you wish to display, and Microsoft picks a display format, which you can change to any of the standard visualization types, like the bar chart I have here.  The reports you wish to see here are pinned.  If you don’t pin them they lurk inn the background. Save this and you are done.

This first version away from Excel offers a lot of interesting features and definitely moves Microsoft closer to the features offered by their competitors.  Dashboard also provides a real reason to use Power BI instead of Excel, a topic I will definitely be exploring more in the future.

Yours Always

Ginger Grant

Data aficionado et SQL Raconteur