Friday, May 25, 2012

PowerPivot Training

A quick word to all those guys in the USA who are looking to get trained in PowerPivot. PowerPivot is the buzz word in the industry right now and companies are drawn to it like bees to nectar because of it’s in-memory and self-service BI capabilities. My company, Mariner in partnership with SQLBI are offering PowerPivot workshops and you can find more details in the flyer below. Feel free to reach out to me in case you have any questions.

Forward to a friend | Visit our website | View as  a webpage  
PowerPivot Training
PowerPivot Training Comes to NC!
    Public Course Now Available
padBe a PowerPivot Power User
In 2 days, learn the basics concepts and progress to complex data models and DAX formulas. At the end of the training, you will know how to use PowerPivot in the real world.  Download the workshop outline.

Ideal for Business & Data Analysts, IT Pros

It is designed for people like you eager to use PowerPivot’s full capabilities. You'll learn the concepts and techniques needed to implement PowerPivot and make critical business decisions using the most popular BI tool on the planet, Excel.

Learn from Leaders
In partnership with SQLBI, Mariner offers PowerPivot workshops in the southeastern US.  Offered on-site at your location or at these new public venues.  Mariner is the exclusive provider of the PowerPivot Workshop in the southeastern US
Register by June 13, 2012 to guarantee your seat.

Mariner is 1 of just 2 companies in the US with access to the training materials developed by PowerPivot gurus and authors, Alberto Ferrari and Mark Russo.  This exclusive partnership enables us to run these workshops for you.
$1,300 per student
includes workshop and training manual

Date & Time
June 18 & 19, 20128:30am-5:00pm
(with an hour for lunch)
Location Aloft Hotel-Ballantyne 13139 Ballantyne Corp Pl
Charlotte, NC

Get directions

Prefer another city?
Let us know!
    Your Trainer, Javier Guillén
Javier is a Business Intelligence Specialist (MCTS / MCITP SQL Server 2008 BI) at Mariner, dedicated to helping people maximize SSAS & PowerPivot (including MDX and DAX). A Microsoft Community Contributor, you may have seen him speak at PASS events and most recently at Carolina Code Camp.  Check out his blog, connect with him on LinkedIn or follow him on Twitter.
    About Us
For more than a decade, Mariner has been helping clients leverage their data - from any data source to any device - through the use of business intelligence, analytics, data warehousing, business scorecards and performance management dashboards. Mariner serves clients in a variety of industries including utility, healthcare, education, media and manufacturing sectors. Singularly focused on providing analytic solutions, we help business people organize, use, analyze and share data to improve sales, customer service, logistics, productivity and financial insight. Mariner is a Microsoft Certified Gold Partner with competencies in Business Intelligence and Data Platform. Mariner-Insight to Achieve.
Copyright  2012 Mariner All Rights Reserved.
2719 Coltsgate Road | Charlotte | NC 28211 |

Facebook Twitter Linkedin YouTube

Wednesday, May 2, 2012

Changing Granularity of Leaf Level Calculations in SSAS Tabular

My company is awesome!!! Everyday that I go to work, I discover something new and exciting from the people around me and come back home thinking – wow, today was just legendary! The best part of the day is when I get to discuss ideas with my colleagues, it is so fulfilling intellectually. So last month, I was having one of those discussions with PowerPivot/Tabular expert and my colleague, Javier Guillen (blog | twitter) on Last Non Empty functionality in multi-dimensional and one thing led to the other. Before we knew, we had discussed a lot of interesting things in both multi-dimensional and tabular about the Last Non Empty functionality and I am pretty sure that you will hear a lot on that field from us soon. Meanwhile, I decided to blog a quick post on one of the basic principles that we will be using for that.

Changing grain in SSAS

Leaf level calculations in multi-dimensional usually impact the performance and hence, are advised to be done in the ETL part. You might be lucky to get away with such sort of calculations in your DSV also as named calculations. But changing the granularity of such calculations from leaf level to an intermediate level usually requires it to be done in the ETL if we are using the multi-dimensional mode (unless you want to impact the performance by a Scope statement or god forbid, a cross join in the calculation script). In scenarios like prototyping where we are still discovering about the data, changing the granularity of the calculations can be expected and it implies a lot of time lost in re-working the ETL. That is where the Tabular mode is such a big boon and I will be illustrating that fact in the following example.

Consider the AdventureWorks database where there is Order Quantity for the Products and Customers, and the client would like to see the distribution of products across the customers. Now you can make a simple numerical distribution measure which is the number of customers that have data for that product by the total number of customer.


The DAX formula is given below

NumericalDistribution:=100 * SumX (
Values ( Customer[CustomerID] ),
If ( [Sum of OrderQty] > 0, 1, 0 )
) / DistinctCount (

Numerical Distribution

Let us look at the results when I analyze the same measure for all the Territories filtered by the Category – Accessories and Bikes

Numerical Distribution Result

Now, let us assume a hypothetical situation where the goal of this year for the Country Sales Managers is to make their customers buy different products and the client wants to analyze the performance of the Sales Managers based on a new Distribution KPI. The numerical distribution measure is not a good indicator for this as it just checks whether the customer has order quantity or not for either Accessories or Bikes. A better indicator might be to assign a weight to each customer based on the different number of products that has order quantity by the total number of products. The formula for the new distribution is given below

The new distribution takes the sum of weights for each customer and divides it by the total number of customer. The DAX formula for the same is given below

Distribution:=If (
DistinctCount ( Customer[CustomerID] ) = Blank ( ),
Blank ( ),
  100 * SumX (
Values ( Customer[CustomerID] ),
Calculate (
DistinctCount ( SalesOrderDetail[ProductID] )
    ) / DistinctCount ( Product[ProductID] )
  ) / DistinctCount ( Customer[CustomerID] )


The results for the above formula is given below

Product Wt Distribution result

We can see that the distribution values have become very low because of the low weight, as it is almost impossible to expect a customer to buy every product. So the client can come back and ask to calculate the distribution at the level of the Country instead of Customer. This will ensure that the distribution is more appropriately calculated. The new formula for the distribution will become

The DAX formula is given below

Distribution:=If (
DistinctCount ( SalesTerritory[CountryRegionCode] ) = Blank ( ),
Blank ( ),
100 * SumX (
Values ( SalesTerritory[CountryRegionCode]),
Calculate (
DistinctCount ( SalesOrderDetail[ProductID] )
) / DistinctCount ( Product[ProductID] )
) / DistinctCount ( SalesTerritory[CountryRegionCode] )

The results for the Distribution formula with the changed granularity analyzed by Country Code and Territory Name is given below

Distribution with Country

Now, the client may again come back and ask for the formula to be re-calculated on a territory level, as that looks more appropriate for them after seeing the data. Take the case of US for example. The distribution value for US is 96.03 but the average distribution based on the territories is 91.90. So the changed formula is shown below

The DAX formula is given below

Distribution1:=If (
DistinctCount ( SalesTerritory[Name] ) = Blank ( ),
Blank ( ),
100 * SumX (
Values ( SalesTerritory[Name]),
Calculate (
DistinctCount ( SalesOrderDetail[ProductID] )
) / DistinctCount ( Product[ProductID] )
) / DistinctCount ( SalesTerritory[Name] )

The result of the formula is given below and we can see that the new values takes the average from the territories at the country level.

Distribution with TerritoryName

Now after making all these changes, the client is happy with the end results. The important thing to note here is that we had this whole hypothetical process completed within half an hour and not days. Consider how much time we would have required to accomplish this in the multi-dimensional mode! Now if you are still in the mood for more DAX after this, make sure that you run by Javier’s latest blog where he uses a similar technique to mimic the MDX Scope behavior in DAX.