Data Explorer is now Power Query

This morning Excel kindly asked me to download the update for data explorer. after doing so new shiny buttons appeared in my Excel Ribbon.

When I have time I will explore a little bit, stay tuned, or not and play around with it yourself 🙂

PowerQueryRibbon

The spaghetti we call Microsoft BI stack

We have many many many BI productsLassard

The Microsoft BI stack is a reference to Microsoft products which make up the “Business Intelligence” and “Business Analytics” offering.

From cubes to tabular models and from standard reports to ad hoc reports. In Between lies a spectrum of reporting and data modeling alternatives. For instance, you can choose Excel for ad hoc reporting tool but you could also use it for standard reporting (something you can share with the rest of the organization).

Having options is a good thing but taking in account the latest developments around Excel (think Data explorer, PowerPivot and PowerView) things have become somewhat …. well overwhelming.

The growth in functionality overlap between these different products have made it confusing and .. well .. downright inefficient.

During the keynote Amir Netz gave on the first BA conference back in April 2013 he explained how chaotic things have become and announced a new goal, simplicity…

However, shortly after this invigorating speech he announced yet another Excel add in “Geoflow”.

Which, I’d like to add, on its own is quit nifty. But looks remarkably like something you should create in PowerView.

I will have to give this some more thought but my humble suggestion would be to start with Geoflow and Data Explorer. I think Data Explorer belongs in PowerPivot (although I like the name Data Explorer better .. come on PowerPivot !? It sounds like a special Power Ranger). Geoflow should be part of PowerView.

So much for my “ranting’s”. Note that I like the Microsoft BI stack, but I see room for improvement :).

Office 2013 Apps

Office store is available here. The Office store is a site from which you can download different apps for office 2013. You can consider apps like “Addins”. Ther are lots of fun apps to explore. From a BI perspective there some nice apps too. I have played around with Bubbles and Bing maps fro Excel 2013.

With the introduction of apps Excel 2013 has become even more versatile allready offering native support for PowerPivot and Powerview. I cant wait to see what developer come up with.

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]);

FILTER(Assignment;

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]);

FILTER(Assignment;

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?

The Microsoft BI shuffle

With the introduction of BISM the Microsoft BI stack as become a little more complex to understand. In this blog post I attempt to clarify BISM. What does it mean, what does it hold and, more importantly, what does it do for me?

Business Intelligence Semantic Model

BISM stands for “Business Intelligence Semantic Model”. To understand why BISM exists I first have to explain that there are two main “engines” which make the Microsoft BI stack. Think of them as the foundation on which a Microsoft orientated BI solution rests.

  1. Vertipaq
  2. OLAP

OLAP is an abbreviation for “Online Analytical Processing”. The main idea is to deliver fast query response by “pre aggregating” frequently used queries. Some also refer to OLAP as a Cube in Analysis Services. Vertipaq, on the other hand, does no such thing. It relies on pure memory power. The more memory you have to more data you can handle. Because all queries are done “in memory” it is very fast. Vertipaq was first introduced with PowerPivot, a free add-in for Excel which enabled users to import large amounts of compressed data from different data sources in to
Excel. In SQL Server 2012 Vertipaq is also available as a “second” engine next to OLAP in analysis services.

Both OLAP and Vertipaq have their own programming language. Vertipaq uses DAX and OLAP uses MDX. OLAP is meant for a multi-dimensional approach whereas Vertipaq is more tabular by nature (which does not mean it does not support a multi-dimensional approach, but that’s a topic for a whole new blog post 🙂 ).

So where does BISM fit in all this?

BISM is designed to be a transparent layer towards client tools so that it doesn’t matter whether the developer has chosen Vertipaq or OLAP, the experience for the end user stays the same.

Every blog post should at least have one picture 🙂 . So, here we go; to sum it up:

So what does it do for me?

As an end user absolutely nothing 🙂 And that’s a good thing. The whole goal of BISM is to hide al the techno mumbo jumbo so that you just .. well, do what you normally do, work with information. For you as a BI Professional / Developer a lot 🙂 Things have shifted, you should know when to use Vertipaq and when to stick to OLAP. How Multidimensional Modeling fits in all this. My advice? Dive right in. Get to know this new world and make it your own. I know this is a rather short answer to give but I promise to write a different blog post covering that question more in depth, it really deserves more then a few lines.

Do you want to know more?

  • More information about how the old OLAP and the new BISM fit in Miscrosoft’s roadmap can be found here.
  • More information about PowerPivot can be found here.

Finally, you can download SQL 2012 CTP 3 here.

Data alerts in SQL #Denali

A new feature in SQL denali is “Data alerts”. Data alerts are a data driven alerting solution that helps you be informed about report data that is interesting or important to you, and at a relevant time. By using data alerts you no longer have to seek out information, it comes to you. More information can be found here.

Users can configure they’re own set of “data alerts” for they’re own set of reports. This means you only recieve a notification (email) when something “relevant” has happened within your report(s). To create a report alert go the the report for which you want to configure one, from the action menu select “New data alert”

This wil open a (silverlight based) new window in which you can cofigure your rule(s):

Once created you can view and manage all data alerts from “Manage Data alerts”:

I have not had the time to really play with it but at a glance I think it is an awesome feature.