SQL Server – Power BI

Power BI refers to the entire suite of Microsoft self service Business Intelligence tools. It includes BI tools for Excel, and the ability to publish data and reports to a website.

Resources:
Power BI Home
Gettting Started

Power BI Excel Components:
Power Query:
Power Map:
Power Pivot:
Power View:

Set Up:
Account:
From the Power BI Home you can sign up for a free trial of Office 365. Once you sign up, you’ll be brought to the Admin dashboard.

Office 365:
You’ll need the latest version of Excel available. To install for the Admin user, go to the ‘Users and Groups’ sidebar link, and then click on your (Admin) user account. You’ll need to assign yourself permission to Office 365 ProPlus.
Back at the Dashboard, click the ‘Download the latest version of Office’ link, then select ‘Office’ from the sidebar. (This step took me several minutes to complete, I needed to refresh the page).
Make sure the 64 bit version is selected. Also, you can’t have 32 bit versions of Office installed, I had to remove those before continuing. Click ‘Install’ to start, and run the EXE. (Install will take several minutes).

Install Add Ins:
Power Query
Power Map : Select the EXE

Enable Components:
Power View – In Excel, create a blank worksheet. From the Menu Bar, select ‘Insert’ then ‘Power View Reports’. Clicking this will prompt you to enable the Power View add-in.

Power Pivot should already be activated for Excel 2013.

Power BI:
On the top menu bar, select ‘Sites’. Some setup occurs after clicking this the first time, so it may take a while to complete. Select ‘Team Site’, then on the next page select ‘Site Contents’ from the sidebar menu, then ‘Power BI’ on the third page.

Reports:
Power Query:
For the remaining examples, I use data from three Customer tables. The script is available on Github.
The sample Customer script will create three tables (Address, Customer, Gender) and populate them with sample data.
Open Excel and create a blank worksheet. Click on the ‘Power Query’ tab. Select ‘Get External Data’ => ‘From Database’ => ‘From SQL Server Database’ and connect to your database. For ‘SQL Statement’ use ‘select * from Customer’. Clicking OK will open the Query Editor and load the data. On the ‘Query Settings’ tab on the right, fill in ‘Customers’ for the name of the dataset. Click ‘Apply & Close’ to save our changes and return to the worksheet. Let’s also click on the Sheet table at the bottom and name this tab ‘Customers’ as well.
Repeat the same steps to import the ‘Address’ and ‘Gender’ records into their own sheets as well.
We’ll return to the first Customer sheet and from the ‘Power Query’ tab we’ll select ‘Merge’ so that we can combine our datasets. Select ‘Customer’ in one drop-down and ‘Address’ in the other. Select the ‘AddressId’ column for both tables and click ‘OK’. We’ll get a new Query Editor with the combined data sets. On the column labled ‘New Column’ we’ll click the arrows icon, and unselect ‘AddressId’ in the column list. We’ll also select the added columns and rename them to remove the ‘NewColumn’ prefix. In ‘Query Settings’ we’ll name the combined dataset ‘CustomersAddresses’. Click ‘Apply & Close’ to save our changes. We’ll repeat the same steps, merging Gender with CustomersAddresses to create a MergedCustomers dataset. We’ll then delete the ‘CustomersAddresses’ dataset.

With the External Data, its is also interesting to explore the ‘Online Search’ option. This gives you the chance to import data from Wikipedia or other online data sources.

Power View:
With the merged spreadsheet open, go to the ‘Insert’ table and select ‘Power View Reports’. We’ll get a report with our Customer data. We’ll click at the top of the report to add a title ‘Customers’. We’ll then click on the ‘ZipCode’ column of the report to sort the record by ZIP Code. We also can remove fields from the report by unchecking the field name under ‘Power View Fields’ or filter our results.

Power Pivot:
Power Pivot will allow us to create a data model for our data set, and allow us to refresh the data from the database.
Open Excel and create a blank worksheet. Go to the ‘PowerPivot’ tab and select ‘Manage Data Model’.
Once the Power Pivot window opens, select the ‘Diagram View’ option under ‘Data View’.
select ‘Get External Data’ => ‘From Database’ => ‘From SQL Server’. Designate the instance and database names that store the test data. We can select our three tables and Finish.

Publish Document:
We’ll return the the Admin dashboard and select ‘Sites’ from the top bar, and then ‘Team Site’. Under ‘Documents’ we’ll select ‘Upload’, navigating to the Customers sheet we saved.
Once we’re back at the team site, we’ll see our Customers document listed under documents. If we click the ellipsis next to the document name, we’ll see a URL for the document to share.
From Team Site, ‘Power BI’ on left sidebar. Click the ellipsis next to the Customers document. ‘Enable’ the report. Selecting the document will allow us to view and modify the shared document, create reports, etc. There are also mobile apps available to view the data from phones and tablets.

Power Map:
Unfortunately, I couldn’t get the Power Map fucntionality to work. The hardware must be able to support DirectX 10 or later. I couldn’t quite figure out if my issue was due to hardware, or running from Remote Desktop.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: