Power BI: Another Player in the Game

First up, Power BI for Office 365

Power BI for Office 365

An year ago till a couple of months ago, when we spoke of Power BI, it meant just one thing: Power BI for Office 365. Power BI for Office 365 is portrayed as a self-service business intelligence (or data analytics) tool on the cloud. The product is basically;

• Excel 2013 with a bunch of add-ons for report authoring,
• and special sites called Power BI sites on Office 365 for sharing and collaboration of the reports created using Excel, plus a few more goodies

Going further, Excel 2013 is the essential component of Power BI for Office 365. It is where all the data extraction, clean-up, consolidation and modeling happens (Power Query and Power Pivot). It is also where the reports and analytics is done (Power View and Power Map). The best thing about this is that all these add-on that I have mentioned are free, and can be used even without purchasing Power BI for Office 365 (PowerMap requires that you have an Office 365 ProPlus subscription). Then, if you need to share it with others, you to put the workbook on a Power BI site. This enables others in your team to see the data and collaborate on it as well. Apart from this, Power BI for Office 365 allows

• analysis using natural language questions (in English) with Q&A,
• creating and managing a repository of queries and data sources (including on-premise), on the cloud,
• and access data from the Power BI Windows Store App so that information is available on the go on Windows tables

The pricing for Power BI for Office 365 currently looks like this, and has more than what I have described here. For more information I recommend reading this, the Power BI for Office 365 service description and FAQs.

And now, there is a new player in the game. And it’s from Microsoft itself…

Doing the Forbidden: Fixing Cubes and Dimensions on Production et al.

Okay, now this could be a completely nuh-uh topic. But in the real world these things can and do happen. Example: There is this cube developed a couple of years ago, deployed on various environments such as QA, UAT, Production etc. Then something goes wrong when some new dimension data has text that is longer than what the field allows – ETL fails. You figure it is a small change to the dimension table: field QuarterName on table DimDate needs to be varchar(15) instead of the varchar(9) that it is. So you go fix it directly on the database – Big mistake when it comes to ALM etc. However, the ETL works. But, when you process the dimension on SSAS you get an error:

Errors in the back-end database access module. The size specified for a binding was too small, resulting in one or more column values being truncated. Errors in the OLAP storage engine.

Build your Own Date Dimension for Power BI – Part 2

In my previous post I talked about and demoed how to get connected to a date data set on the Azure Marketplace. The purpose was to access date information to build a date dimension for a Power BI solution. In this post I will focus on how one would pull in data from the said data set through Power Query, massage it to suit my requirements and finally model it in Power Pivot for consumption.

To do this, let us make use of a simple scenario where I want a date dimension with Years, Months and Dates that go from Year 2000 to 2015. There are two major steps that I will perform:

1. Extract, Transform and Load the date data to Power Pivot
2. Structure the loaded data table into a Date dimension

Build your Own Date Dimension for Power BI – Part 1

The most common dimension used in a BI solution is the Date dimension. This is obvious since almost any type of analysis involves looking at data from some aspect of date; year, month, or even hours and minutes in some cases. In traditional BI scenarios, a Date dimension table is always created as a practice, as is an ETL to populate it. The usual case is a stored procedure which generates a range of dates between two given dates, and scheduled to run on a regular basis: yearly, quarterly etc.

In the case of a Power BI solution, one would not have the luxury of a date-generator stored procedure. Instead we could go for a purpose-created Excel file with all necessary fields with a range of dates. The issue with this would be that if you plan to use it across various disparate solutions, the Excel would have to be quite comprehensive with a large range of dates to support various scenarios. Locality will also have to be taken into consideration, such as a British calendar or a US one for example.