Wednesday, June 06, 2012

Combining Two Sources in PowerPivot

I was at the Microsoft BI User Group meeting on Tuesday 6/5 for a presentation on using Microsoft's PowerPivot as a tool to help get data into business user's hands to help flush out requirements as part of  an Agile BI approach.

I thought that I would share an approach that I have used to help bring together data from two completely different data sources to present a common view/ability to slice/dice the information.

WHEN Familiar with PowerPivot THEN
I will presume that by chance that you have landed here you are somewhat familiar with what a great tool Microsoft is giving away as a companion to Excel. You might be interested, continue reading.
WHEN Interested in PowerPivot THEN
This is a more advanced example, a PowerPivot introduction may be in order.
You've likely already abandoned your reading by now.

Disclaimer:  This demonstration does not include any factual information.  Quantity and Dollars have been manipulated through the clever use of the RAND() expression.  Locations have been created from geographical examples of cities and states selected from the listing of US Farmer’s markets provided by  My order modes were completely non-factual and are not intended to portray any business direction for any company I may be associated with.  Besides, who continues to use Faxes anyway? (That last part is a joke that may not make any sense until you see the finished results.)

The Challenge:  Put together a demonstration that combines two different data sources making them "look" like a single measure along "conformed dimensions."

Add Data Sources

One of the strengths of Microsoft PowerPivot is the ability to add multiple data sources.
I have used perhaps a more advanced technique manually constructing my data sources by "Specify a SQL Query", naming them Data_Set_One

and Data_Set_Two
If you look closely at the queries, you will see that I am using the RAND() expression to make up my demo quantity and demo dollars.  I have purposely aliased my source columns differently because it really doesn't matter what they are called.  (Remember, this is a quick solution to demonstrate that one could make two different sources look like one.)


Couple of things that maybe I should be embarrassed to share because they may seem so self evident, but have been very helpful for me:

You can revisit and modify your data source query: Design tab, Table Properties
You can add additional tables from any existing connections: Design tab, Existing Connections
I am obtaining several tables from three different database connections.  Each time I may need to add a table from a previous connection, choose Existing Connections, then "Open" the applicable connection
Note, I took my image before correcting the server/database on my third connection. In practice, I attempt to only create connections than are required reusing existing connections if using the same database.

For each of my common "dimensions" I have used the combination of my three database connections to bring in tables for information about time of day, business dates, products, locations, and order modes.

Add Relationships

Going fast here: Once I have added my common "dimensions" I relate them to my two different data sources.  Both Data_Set_One and Data_Set_Two have a relationships to: Date, Time, Products, Location, and Order Mode:

Nice and Tidy

I now typically do some house-keeping:  I tend to keep the presentation area for the Excel Pivot Table clean. I will hide entire tables and hide any fields, ID’s, or values that could cause the potential for confusion.

You can hide an entire table: Right Click on the tab for the table, choose "Hide from Client Tools"
You can also hide columns within your tables: Right click on column, choose "Hide from Client Tools"

Create Calculations

For ONE of my data sets, I create two calculations using a DAX formula to sum the data from the two data sets. (Even though a column or table may be hidden from view, it can still be used in your DAX formulas.)
Demo Quantity:=sum(Data_Set_One[Demo_Quantity])+sum(Data_Set_Two[Demo_Quantity])
Demo Dollars:=sum(Data_Set_One[Demo_Dollars])+sum(Data_Set_Two[Demo_Amount])

Note in my Demo Dollars measure that I am adding my previously and purposely aliased columns called Dollars in one and Amount in the other.  Did so to show that so long as you know what you can add together, it doesn't matter much what they are named.

I have added these two measures to Data_Set_One and hidden all of the other columns:
When completed adding the pivot table to my worksheet, I have a fairly clean looking presentation of information from two completely different data sources. This gives the recipient the opportunity to slice/dice re-arrange the information based on the common relationships to time, date, location, etc...

Notice there is only one displayed data set with two calculated measure: Dollars and Quantity.  

When I first delivered a real example of this, I did so to in an attempt to demonstrate the challenge of trying to provide the business measure based on what they were asking for.  My hope was that although I could deliver what was being asked for, I didn't think that it would satisfy the question being asked.  In one system the order mode is a reflection of where the product was consumed, the other a reflection of how the order was placed or instructions for preparation.  I also wanted an attempt to demonstrate the challenge with our product dimension: both sources of information have the same product ID, but different levels of hierarchy. 

Thursday, December 01, 2011

Lather, Rinse, Repeat

Turn away now if you landed here expecting to read about bike stuff.

There are 60 started but unfinished drafts languishing under "Posts."  I miss writing, more I miss the clarity I once felt when writing. I found the experience quite fluid despite what anyone reading might think otherwise.  I keep thinking that someday it'll come back, I'll be able to concentrate a thought long enough to sort through what's troubling me.  Compounded with more things troubling me, it's a tortured life I live.

I miss the honesty of being able to celebrate an adventure, more I miss the adventures.  I've managed some comparatively shorter trips, but by the time that I sit down to share I'm frequently suffering the consequences.  Summer wasn't terrible, by the time that I sat down with fingers on keyboard to find the positive bit to share, too much sounded negative.

Three LONG years have gone by filled with chronic pain.  The effort and energy that I put into trying to find motivation and attempts to ignore the nagging discomfort are exhausting.  During one of my sessions with a pain psychologist we talked about the ability to suppress thoughts about discomfort.  I tried to explain how I have done several 24 hour (or more) events during which there are obviously times when one's mind might turn to thoughts about a sore bum, raw hands, blackened toenails.  Ignoring that shit is easy, it's temporary and after a couple of days everything is normal again.  This, well, I wonder if this is new normal.

I have grown tired of waking up every day with a headache or some mornings laying in bed in tears from the pain while building the energy and commitment to force myself to move on with the day.  I eventually dust myself on, take a handful or mix of anti-inflammatory drugs and move on with the day. I've tried to resume regular life activities, frequently over-doing it.

We are into December now and the weather is turning cold which seems to makes everything worse.  Weeks go by without riding a bike, and I simply cannot bear the pain from the ankle to get out an run.  It's killing me! I need to get outside, some exercise, some fresh air, an endorphin release.

Sufficiently depressing? Yup.

Here's the upshot; being almost an addictive person, I've turned to reading and learning... work stuff. Perhaps to a fault of working too much, but it does give me some sense of accomplishment and I need that.  I only write this all today as I contemplate sharing more about things of a technical nature.  I feel like I have A LOT to learn, could use a good coach, but I think that I might have some things to share.

Hang on, prepare to be bored on top of the depression.

Blog Archive