What is Power BI ?

Much like the power rangers Power BI is packed with lots of features called Power something something :)

Much like the Power Rangers Power BI is packed with lots of features called Power something something ūüôā

It has been several months since the release of the preview version of Power BI. Since then lots of features have been added. Recently Microsoft also released its pricing plan for Power BI. With the final release closing in I thought it could be helpful to explain what Power BI is now.

It is important to note that Power BI is still in preview. You can sign up for the preview here.

Power BI is a cloud-based Microsoft Business Intelligence solution which supports on premise data refresh and mobile BI (Windows and iOS devices).
Power BI was first introduced at the 2013 World Wide Partner Conference:

At the heart of Power BI lays Office 365, specifically Excel 2013 and SharePoint 2013 online. Power BI encapsulates three major BI themes:

  • Data Discovery & Access
  • Data modeling
  • Data visualization

Data Discovery & Access

Within Excel 2013 it is possible to extract and transform data from various different sources like social media, the internet, files, CRM online and many more. The feature in Excel that enables you to do this is called Power Query. Apart from external sources Power Query also supports searching on premise organizational data like your CRM system. Selections made through Power Query (called queries) can be shared with others. The discovery of organizational data is al manageable from within Office 365 called the Power BI management dashboard.

Data modeling

Once you have made your selection(s) within Excel 2013 it is possible to further ‚Äúsculpt‚ÄĚ your data so that it can be better consumed by others. This is called ‚Äúdata modeling‚ÄĚ and is done within Excel 2013 using a feature called Power Pivot. Power Pivot enables you to extract and enrich billions rows of data without the performance degradation you would normally get using just native Excel. In Power Pivot you can add hierarchies, add extra columns, hide columns, rename columns and add calculations (churn, ROI etc).

Data visualization

Power BI currently supports two kinds of visualization:

  • Excel Pivot tables and charts
  • Power View

I won’t go into detail about the pivot tables and charts. Many of you probably have a good idea of what it is all about.

Power View is a feature within Excel 2013. Power View is essentially an interactive canvas that allows you to display charts, tables, maps, and slicers in one dashboard window. What sets Power View apart is that the components in a Power View dashboard are inherently linked, meaning they all respond to one another. Select a region in one chart, and the other components in the Power View dashboard automatically respond to show you data for only that region. Many of these usability enhancements are ‚Äúout of the box‚ÄĚ but quite a few can be enhanced using the Power Pivot data model. An example; in Power Pivot you can categorize data such as, City, Postal code, Image etc. When using Power View to visualize this data it detects these categories and will act accordingly, in the case of city it will plot data on a map.

Other important features to note

Scheduled Data Refresh is supported in Power BI. Which means that once you have uploaded your Excel workbook its data connections are refreshable regardless of its origin (cloud or on premise).
Q & A is a feature within the Power BI site. It enables users to type in questions which Q & A will interpret and answer using different Power View visualizations.
Power Map is a 3D visualization add-in for Excel for mapping, exploring, and interacting with geographical and temporal data. At this moment is it not supported in Power BI sites. Watch this video to see it in action.

Demo’s you might find interesting

Excel 2013; I want more details!

In Excel 2010 it was possible to increase the number of rows Excel would return after initiating a drillthrough. In Excel 2013 this option seems to be disabled

Excel 2013 connection propperty windowOn the computer I worked on the dutch version was installed ūüôā¬†

I work with many Excel pro’s, so you can imagine the furious reactions I got.¬†It seems other people have found this “issue” too and posted about¬†it on connect.

The good news? There is a workaround (posted by “Kwenix” on the connect item mentioned above) and yes it works!

To safe you time I repeated the described steps below:

  1. Make a backup of your Excel file
  2. Rename xlsx to zip file
  3. Open zip file using 7z
  4. Locate connections.xml file in ‘xl’ folder
  5. Hit F4 in 7z to edit file
  6. Locate rowDrillCount=”1000″ and change to desired value
    upon saving 7z will update file into the zip file
    rename zip to xlsx

And thats it! I urge everyone to vote on this connect item!

Point in time analysis perfected: Part 2

It is funny how some ideas are not thought of but just fall in to your lap :). My answer to a question posted on my last blogpost resulted in a more efficient solution to “point in time analysis” then the one I posted about. I think this particular improvement on a exisiting solution is a nice example on how most solutions are not only¬†about applying technique but mostly about applying (business)logic.

How the old version looks like:

=CALCULATE(SUM(Assignment[Planned hours]);


Assignment[Valid from date] <= MAXX(VALUES(Period); Period[Date])


Assignment[Expiry date] >= MAXX(VALUES(Period); Period[Date])



How it works:¬†Give me the¬†total “Planned Hours” of all assignment rows in table “Assignment” of which the “Valid from date” is smaller or equal to the selected “Period Date” and the “Expiry Date” greater then or equal to the selected “Period Date”. This solution requires a unrelated date dimension table (in this example it is called Period). This approach needs a unrelated date dimension table.

How the new version looks:

=CALCULATE(SUM(Assignment[Planned hours]); FILTER(Assignment; Assignment[Valid from date] = MAXX(VALUES(Assignment[Valid from date]); Assignment[Valid from date])))

How it works: Give me the total “Planned Hours” of all assignment rows in table Assignment of which the “Valid from date” is equal to the max “Valid from date”.

The main difference? No second MAXX iterator! Which is one less table to iterate. On large tables this can make a huge difference. Another perk you get from this solution is that you do not need to have a unrelated date dimension.

Point in time analysis perfected

A while ago I wrote about how you accomplish this using DAX in PowerPivot. You can read about it here. It is useful but I always thought there were two major drawbacks to that approach; Code length and Usability.

The expression I used was huge! No I mean really huge. This is due the number IF statements I used, one for each possible combination of user selections in the filter.

So I decided to¬†rewrite the expression with these goals in mind; shorten the code, and thus make it easier to understand, and making it less error prone. This post is all about sharing this new-found knowledge with you ūüôā

The business scenario
An organization wants to have insight in how and if planning assignments change for their employees, the goal is to minimize last-minute changes and thereby creating a more manageable process. The organization decides to harness the power of their data warehouse which tracks assignment changes using the Kimball type 2 approach.

Our goal for the user is to be able to select any period in time and get the current / valid planning for that period without the use of complex logic (technical or functional), aka “point and click”.

Getting in the details
Below a snippet of our large assignment fact table in our data warehouse

In short: Ian Smith’s planning has changes two times for the same assignment. Each change is marked with a valid from and expiry dat marking the period in which that assignment line was valid. John Snow’s planning changed one time.

Below a snippet of the separate period table I imported. It can be of any source in this case it is a copy of the data dimension table in our data warehouse.

No relationship is created between the two tables in PowerPivot. The period table is purely used for the user to select a period for which he/she wants to see the valid planning.

On to the DAX
This is the measure I created:

=CALCULATE(SUM(Assignment[Planned hours]);


Assignment[Valid from date] <= MAXX(VALUES(Period); Period[Date])


Assignment[Expiry date] >= MAXX(VALUES(Period); Period[Date])



The CALCULATE is there to be able to influence the FILTER context. I could also have used SUMX. The FILTER returns a table in which all assignments are filtered; each assignment-line¬†returned has a “validfrom”¬†date smaller or equal and a “expiry”¬†date larger or equal¬†to the max date of what the user selected in the table period. The VALUES function returns the distinct values of what the user selected, in combination with the function MAXX that is always one row.¬†The result being that the FILTER returns 31-jan-2009 when the user only selects 2009 or 31-jan-2009 when the user selects 2009 and month january.

The point is the user is free to see valid planning at year level as well as on day level or any combination in between without the use of complex IF statements. Pretty nifty huh?

Using a custom data refresh schedule in #PowerPivot for #SharePoint

A PowerPivot workbook deployed to SharePoint 2010 becomes extra powerfull because it enables you to use it as a “information source” for all other reports (wether they be reporting services report using “Odata feeds” or “Excel services” reports). One important feature to configure when you want to use PowerPivot as a “information source” is the feature to schedule data refreshes.

As some of you know that feauture is available, well… sort of. The “out – of – the – box” data refresh feature does not support intervals smaller then one day. I have several business scenario’s in which it is required that the information is near real – time (like every 5, 10 or 15 minutes).

Determined to find a way I consulted our local incompany SharePoint Guru and low and behold.. we found a solution. Here it comes.

First, getting lost

Being the “hackers” we are ūüėȬ†we re-enginered the dll responsible for handling data refreshes (when I say we, I mean my colleague. I mostly sat next to him looking pretty). From my point of view it was like looking at a ping – pong game, hopping from one class to the other. After some minutes (we are of the impatient type) we thought we would never find it.

Then, someone got smart

During our hopping journey we did see calls being made to the database. My colleague suggested to have a look in the PowerPivot application database on the SQL Server… We found the holy grail!

We found several tables of which the following are usefull, at least to our knowledge:

  • DataRefresh.Items
  • DataRefresh.Runs

The “Items” table holds each published PowerPivot workbook. The “Runs” tables holds the PowerPivot scheduled refreshes. In short; when adding a PowerPivot workbook registered in the “Items” table to the “Runs” table with status “W” (waiting) it will be run the next time the SharePoint job scans the table.

So, guess what we did. We made a stored proc and a SQL job to schedule a custom data refresh every 10 minutes (which could be any interval since we use the schedule of the SQL job). The result is shown below:

Some pointers;

  • Keep the “Runs” table small. there is a maximum of records it can hold. I made sure that with every run it also made sure that there¬†are maximum of 10 entries.
  • Make sure no other entry of the same¬†PowerPivot workbook exists with status “W” or “Q”, if so cancel it and set its status value to “C”. (See the¬†DataRefresh.RunResults table¬†for details).
  • The “Items” table not only contains the PowerPivot workbook entry but also meta data like the application server on which it is deployed. Copying this entry to the “Runs” table means “hard coding” on which application server the data refresh is run.

Do not try this at home, well at least not untested!

The solution described here is¬†NOT SUPPORTED BY MICROSOFT. Be aware of the fact that it is a “hack“. We are planning on implementing a more elegant and “supported” way using the SharePoint Object Model.