Sunday, August 18, 2013

Self-Service & Corporate BI with Power Pivot and SSAS Tabular Slides

I posted the slides for a Balanced BI Approach (Self-Service & Corporate BI with Power Pivot and SSAS Tabular) below to SlideShare.

There is also a link to a webinar I did on this as part of Pragmatic Works Free Training on the T's (Tuesdays and Thursdays) below and contains a demo where I create a Power Pivot solution from the ground up and upgrade it to a Corporate BI Solution in SSAS Tabular in a few easy steps.

Balanced BI Approach (Self-Service & Corporate BI)

Hope this helps out those who want to use these technologies together!

Monday, June 3, 2013

PowerPivot CAN do Compound Key Relationships

Recently ran into a situation where PowerPivot was automatically discarded as a tool because it doesn't natively handle compound key relationships.  It can do this easily and will show you how to achieve this. 

If you are not familiar with PowerPivot then Rob Collie's post is an excellent description:

Point No. 6 is the main reason why I've seen folks use it. 

"A Way to handle Massive Volumes of Data in Excel

Normal Excel caps out at 1 Million rows of data per sheet.  PowerPivot goes well beyond that and for most people, you will find it actually HAS no practical limit.
For instance, here’s a workbook I like to demo that has 161 Million rows in a single sheet..."

PowerPivot and SSAS Tabular do not permit compound key relationships natively in the data model. You can achieve this though quite simply and all that is needed is to create a calculated column that concatenates the columns that comprise the compound key. 

Here's how you can do it with illustrations:

Let's look at some sample data from two tables (Sales and Store Info) where I have StoreNo and State as my two fields (compound key) that uniquely identifies a store's sales for the day (label on sample data should be day).

What you can do is create a new column and concatenate the two fields StoreNo and State as below.  Two ways you can concatenate fields in PowerPivot is by using CONCATENATE() or simply using &.

CONCATENATE() limits you to two fields but & can do more.

Simply do the same thing in your other PowerPivot Table as below and I've renamed the column to StoreNoState.

Now you can create a relationship between the tables in the Diagram View as below:

Important to note the way you create your relationships in PowerPivot or SSAS Tabular is important and a simple rule of thumb is to always go from a Measure table (Sales) to a Non Measure table (Store Info) or Fact tables to dimension tables.

That's it!  Now you can move forward as business as usual and can slice Sales by Store Size and State as the two tables are now related:

Hope this helps those out with Compound Key struggles in PowerPivot!  This same process can be done with tables in SSAS Tabular as well.

Thursday, May 16, 2013

DAX Literal Date Syntax changed with Compatibility Mode 11.0.3

Just a quick post here about a situation that came up with literal dates in DAX when a client downloaded one of my DAX for T-SQL folks scripts and found that it didn't work. The statement is this:

evaluate (

FILTER('Date','Date'[Full Date] = "2013-04-01")


You receive an error stating a data type mismatch and the above doesn't run.

The problem is with the compatibility mode your BISM Tabular model is in.  In 11.0.0 the above syntax works fine but with 11.0.3 you get the mismatch error.

The statement above needs to use the FORMAT function with 11.0.3 as below:

evaluate (

FILTER('Date', FORMAT('Date'[Full Date],"YYYY-MM-DD") = "2013-04-01")


Hope this helps and included the actual code below to download.

Monday, May 6, 2013

Measures, Non-Measures, or a Combo in Power View

If you are new to using Power View you may notice that there are images on the left of the available fields in the Field List:

When you are creating your visualizations it is important to know what numbers you can see and what they can be sliced by:  E.g. Product Profit by Sales Number.

Numbers are Measures represented by the Calculator. E.g. Product Cost & Product Profit above.

Slicers are Non-Measures represented by no image. E.g. Sales Category above.

Combo is represented by the greek letter Sigma. E.g. SalesNumber above.

So lets drag them into a blank Power View report to demonstrate the above.

Product Profit sliced by Sales Category and Count of SalesNumber.

When using the combo field SalesNumber I chose to a Distinct Count of SalesNumber to show SalesNumber as a Measure to answer the business question how many Sales were attained. This can be done in the Layout Section which is directly below the Fields List section in Power View.


To change the combo field SalesNumber from a Measure to a Non-Measure or Slicer, simply select the "Do Not Summarize" option and you will notice it change in Power View as below to answer how much profit per transaction was attained:

I will be explaining this in further detail and covering a lot of other material about Power View this Wednesday night May 8, 2013 at the MSBI South Florida User Group event at 6 pm. More info on the event is here and hope to see you all there!

Thursday, March 28, 2013

Slides Uploaded from recent Speaking Events: SFSSUG, MSBISF, Pragmatic Works

I know its a been awhile, and know a lot of folks have asked so here are the decks from my recent speaking engagements.  I have also realized I need a new template for presentations :-) It's added to my list!

South Florida SQL Server User Group (SFSSUG) - Conquer Reporting by Scaling Out SQL Server


Microsoft BI User Group of South Florida (MSBISF) - SSAS Tabular and Self Service BI - The DynamDAX Duo

Pragmatic Works Free Training on the T's - Self Service Visualization with Power View

Thursday, February 28, 2013

Creating Images in Your Power View Visualizations

Thank you for all of the nice emails, direct messages on twitter from the webinar I gave on Power View as part of Pragmatic Works Free Training on the T's on Valentine's Day.  And yes I did play Mr. Barry White to get you in "the mood" for some Power View awesomeness.  You might see a lot of other Power View presentations, but doubt Barry White will be part of those!

A lot of you also asked about the process to add images to your BISM Tabular or PowerPivot model so figured I would write it up.  It can be done in 5 simple steps.

I added images to the Adventure Works 2012 Data Warehouse database on DimProductSubCategory

1. Alter the DimProductSubCategory table and add a column to store the URL.

2. Create a folder on the SharePoint site to store the images you wish to associate to the product sub categories.  You don't have to use SharePoint as your location but should make sure the permissions will work for your chosen URL location.

3. Upload the images to the SharePoint folder or your chosen URL Location.

4. Update the DimProductSubCategory table in the database with the URL for each SubCategoryPicture.  Now I named each image exactly as the EnglishProductSubCategoryName that you find in the table so I could easily produce a URL for all of the product sub-categories with a simple update statement.

5. I used a BISM Tabular Model as my source for Power View so I just need to import the new column from its underlying SQL Server Database into the BISM Tabular model. This can easily be done by selecting Table > Table Properties > Check the box for the new column SubCategoryPicture.

On the SubCategoryPicture column set Image URL properties to true.  Save Your Model and Deploy it.

Once Deployed you can embed images in your Power View Visualization like below:

Hope this helps you create images in Power View easily!

Friday, February 8, 2013

Fall In Love with Power View this Valentine's Day

Fall in love with Power View this Valentine's Day. Thursday February 14, 2013 at 11 am est I am presenting Self-Service Visualization with Power View as part of Pragmatic Work's Free Training on the T's (Tuesdays and Thursdays).

In this session I will give a brief history of Self-Service Visualization and some tools out there today that facilitate it. I will then introduce Power View and the two ways it can be used; SharePoint 2010 (or 2013) and now Excel 2013. I will then cover what sources can be used to create a Power View report then jump into a demo and create a few visualizations from a blank canvas to demonstrate how easy, yet powerful this visualization tool really is.

Hope you can attend! You can register by clicking here.