This is the third article from Power BI Designer series. To fully understand this article you need to read my previous posts “Build Your First Report in Microsoft Power BI Designer Part 1, Basics” and “Build Your First Report in Microsoft Power Bi Designer Part 2, Make it More User Friendly” as well. In this article I explain how to publish your predefined reports on www.powerbi.com website which is free. So after publishing the reports, you can create flashy reports very easily. By very easily, I mean it! Creating dashboards is even easier than dragging report objects and dropping them somewhere on the tool! I’ll explain how that is possible. Actually, it is all about the awesomeness of the online BI Designer.
Frist of all, you need to create an account in www.powerbi.com. Unfortunately, you’ll need to have a corporate email address that means you’re NOT allowed to use free email accounts like MSN, Hotmail, Yahoo, and Gmail and so on. But, if you’re a student with a valid university email address or if you’re an employee with a corporate email address, then you’ll be fine.
Continue reading Build You First Dashboard in Microsoft Power BI Designer, Access Analytics Everywhere
In this post I would like to explain more details about Power BI Designer features. In the previous post you learnt how to create some very simple reports. However, those reports were just for testing general features of the tool. For instance we didn’t even play with very simple features like renaming the dimension or fact tables and members to user friendly names. In this article not only do I explain some of the simple ones, but also I’ll go through some of the more advanced ones.
Again, as per my previous post, I’m using AdventureWorksDW2012 as a source database. We imported “Internet Sales” into the designer and we created some reports and one new page and we saved the reports on disc. So we have all the requirements on hand. Let’s go…
Making names more user friendly:
Continue reading Build your First Report in Microsoft Power BI Designer Part 2, Make it More User Friendly
First of all I would like to briefly explain Microsoft Power BI Designer. Then we’ll see how easy we can create a report using designer. I will use Adventure Works DW database as the source database.
Microsoft Power BI Designer is basically an integration of Power Query and Power View. Saying that the tool is still a preview version and it’s NOT actually a released product we’ll expect to see more features when it’s released. Some features like PowerPivot models expected to be available to the release version. At the moment PowerPivot models are not available in the designer, but, hopefully Microsoft will add it to the tool. So I added an idea into BI in SQL vNext as I believe it would be great to have PowerPivot functionalities included in the release version of the product. We’ll see what happens.
Let’s have a look at the tool. At the first look, I would like to say it’s an amazing tool integrating lots of awesome features all together with ease of use. It’s so fun to use the tool to create very effective and flashy reports in a short amount of time. First of all you need to download the designer from here. Install the designer and open it. I’ll use AdventureWorksDW2012 as the source database.
Open the Microsoft Power BI Designer Preview. If you want to get more familiar with the tool click on the videos on the startup screen.
· To connect to SQL Server click on “Get Data” or “New Source”
Continue reading Build Your First Report in Microsoft Power BI Designer Part 1, Basics
In this post I express an easy way to refresh a PowerPivot model dynamically based on SQL Server Stored Procedures. Let’s start with SQL Server Management Studio (SSMS) 2012 and use Adventure Works 2012 database. Run the following script to see the results in SSMS:
exec [dbo].[uspGetBillOfMaterials] 727, ‘2009-01-02’
First parameter: Start Product ID
Second Parameter: Check Date
Now we want to see the results for the following script:
exec [dbo].[uspGetBillOfMaterials] 762, ‘2009-01-02’
Now we want to do the same with PowerPivot.
Continue reading Dynamically Passing Parameters to a SQL Stored Procedure from PowerPivot Using VBA
In this post I want to share my experience with refreshing data in Power Query and the way it affects loaded data to an Excel worksheet. I was loading sales data from SQL Server and I noticed that there is no easy way to define a 1000 separator in Power Query. So I decided to do use the power of cell formatting in Excel. So I loaded the data to an Excel worksheet and I spent some time to do some cell formatting including thousand separation and currency formatting and so on. But, as soon as I refreshed the data source from Power Query all of the defined formatting were gone. I also spent some time to do some special formatting like 1000 separator or dollar sign directly in Power Query, but, it seems Power Query team in Microsoft didn’t consider formatting as a priority yet. Even with setting up locale and language settings in Power Query you cannot define the 1000 separator formatting directly in Power Query. As I needed the formatting features included in the solution I should have resoled the problem anyhow.
Let’s start with loading some simple data from SQL Server using AdventureWorks2012 database into Power Query. I’ve used “Production.Product” table to show you how Power Query treats a column with “Money” data type.
· Open Microsoft Excel and go to Power Query tab
· Click on From Database-> From SQL Server Database
Continue reading Resolving Format Cells Change after Refreshing Data Sources in Power Query
Today I want to explain how you can pass parameters to a SQL Server stored procedure. I myself was looking for a way to pass parameters to a SQL Server stored proc from Power Query. I spent a lot of time to search for a good article over the internet that explains how I could pass parameters to a stored proc from Power Query and show the results in Excel. But, I couldn’t find that much information around this as I expected. So, I decided to do some work around and you can read the results in this post. To simplify the solution, I’m going to use uspGetBillOfMaterials stored procedure in AdventureWorks 2012 database. The stored procedure accepts an integer number as ProductID and a date as CheckDate. So we need to pass two parameters to uspGetBillOfMaterials to get the results.
If we execute the stored proc in SSMS using
exec [dbo].[uspGetBillOfMaterials] 727, ‘2009-01-02’
, we’ll get the following result:
Now, lets go to do some works on Power Query. So open Microsoft Excel and go to Power Query tab and select SQL Server database.
Now type Server, Database and SQL Statement, then click OK.
Continue reading Power Query and SQL Server Stored Procedures