« November 2009 | Main | January 2010 »
We've all made big mistakes in our career - and so we all know that one thing worse than making a big mistake, is making a big mistake that goes undetected for a long time!
The following is an excerpt from a lesson plan in our SetFocus SQL Server and Business Intelligence Master's Programs.
Consider the following: suppose we have 3 tables in a SQL Server database: JobMaster, JobHours, and JobMaterials. They represent a simple example of multiple one-to-many relationships. A single job in JobMaster can have multiple timesheet rows (or none at all) in JobHours, and multiple material purchase rows (or none at all) in JobMaterials.
So our tables might look something like this:
So there are four possibilities:
Now suppose we need to write a query that retrieves each Job in JobMaster, along with the sum of Hours Worked (from JobHours) and the sum of Material Purchases (from JobMaterials). The result set should show all jobs - even those with no child data, and would look something like this:
JobDescription TotalHoursWorked TotalPurchaseAmount
Many SQL developers, recognizing that a LEFT JOIN is required to show all jobs, might write the code as follows (below). The code performs a LEFT JOIN between the parent table and two child tables, and aggregates (SUMs) the HoursWorked and PurchaseAmount from the two tables. This produces the result set in the result set window (below).
At first glance, the output above appears correct.
At this point, some might be tempted to "bless" the code and results. Surely, the one Job we didn't test (with both hours and purchase amount) is the simplest scenario, and "has" to be correct, right? :)
Well, you probably see where this is going! Take a closer look at the raw data for Job A.
Further, we notice something else - there were 4 Job Hour rows for Job A, and the material purchase aggregation was off by a factor of 4.
Additionally, there were 2 Material Purchase rows for Job A, and the Job hour aggregation was off by a factor of 2.
COINCIDENCE? No, it's not a coincidence. As it turns out, when we use multiple JOIN statements (either INNER or OUTER joins), and try to perform multiple aggregations on multiple one-to-many relationships, you risk overstating the aggregations. In this case, the 2 instances of a JOIN between JobMaster and JobMaterials (for Job A) wind up joining 2 instances of Job A to the Job Hours table - thus the double-counting of hours. Likewise, the 4 instances of a JOIN between JobMaster and JobHours (for Job A) wind up joining 4 instances of Job A to the Job Materials table - thus overstating the aggregation of material purchases by a factor of 4.
I know developers who have written the same general code as the code above - and didn't realize until much later (usually, when a user or customer discovered high number) that this was a problem waiting to happen.
OK, so we've uncovered the problem - what's the solution?
Before we look at a solution, we need to realize one thing - just like a single long code listing, it's often difficult (or impossible) to solve a problem in one self-contained SQL statement. In this specific situation, we need to "pre-aggregate" JobHours and JobMaterials independently of each other, in some type of subquery. Then we can JOIN the pre-aggregations to the JobMaster as a 1-1 relationship.
In the code below, we can use the Common Table Expression capability in SQL Server 2005 to create 2 CTEs that pre-aggregate Hours and Materials. Then in the next line of code, we perform LEFT JOINs between JobMaster and the 2 CTEs, and that produces the correct results.
Of course, there's often more than 1 way to write a query. Some might prefer to write a 2 correlated subqueries, each of which retrieves a single aggregated amount for every job in the outer query (see below). Of course, there are always debates about whether a correlated query might run a bit slower and might take up additional resources. We cover these issues in the Master's Program classes.
If there's a moral of this story, it's that sometimes our safest assumption might be the very root of the problem!
During our PerformancePoint Server class in the SetFocus Business Intelligence Master's Program, we cover analytic charts and analytic grids - two powerful ways to incorporate image-style output (i.e., output that can fit on a single screen) into a PerformancePoint Server dashboard. We also cover the role of MDX in coding the necessary calculations to support the charts/grids.
One example is the screen shot below, where we create a dashboard page that allows the user to select any product in the Product Hierarchy (either a high level category or a detail level SKU). The page then generates a chart that shows the following:
It should be noted that PPS analytic charts are smart enough to plot the percentages on a dual Y-axis, since a % is on a different scale than the revenue measure.
The dashboard page also generates a data grid recap that shows all of the siblings of the selected product (i.e. all of the products that share the same hierarchical parent of the selected product), using the MDX SIBLINGS statement, and the % of parent Sales for by month. The data grid recap also navigates up the product hierarchy from the user selection, to show the same % of Parent calculation for each parent level. So in this example, we're showing the following:
The chart helps to spot upward or downward trends in a product's share, and also to verify any trends based on seasonality over the two year period. The data recap at the bottom provides additional information on other products that share the same parent. A category manager might use this to (among other things) spot product cannibalization, where product sales decrease due to sales of a newer product.
In the chart below, we see that sales for GamePlace 2 took two significant nose-dives in 2005: in July of 2005 and again in September/October 2005. We can check the analytic grid at the bottom to see which products had a % of Parent increase during that same time period.
OK, so where does the MDX come in? Well, first we need to create calculations to support [Pct of Parent] and [Pct of Parent LY]. We can use Analysis Services to place the following MDX calculations into the cube area:
CREATE MEMBER CURRENTCUBE.[MEASURES].[Pct of Parent] AS
[Measures].[Dollar Sales] /
( [Measures].[Dollar Sales],
[Product].[ByCategory].CurrentMember.Parent)
CREATE MEMBER CURRENTCUBE.[MEASURES].[Pct of Parent LY] AS
( [Pct of Parent],
PARALLELPERIOD([Time].[YQMD].[Year], 1))
What's great about the first calculation is that it will for any level in the product hierarchy. If I run the chart for a product SKU, the MDX code will use the $ Sales for the SKU's parent in the denominator: likewise, if I run the chart for a product subcategory, the MDX code will use the $ sales for the product subcategory's parent in the denominator. So we can re-use this calculation for any product category level.
What's so great about the 2nd calculation is that we can use the MDX function ParallelPeriod to go back one year, and express the first calculation in terms of the "same time period a year ago". So if we're looking at a measure for Feb 2005, this code will give us the measure in terms of Feb 2004. If we're looking at a measure for Q2, 2005, this code will give us the measure in terms of Q2, 2004.
Note how neither calculation receives the time period (in this case, Months along the bottom axis) as a parameter - this is because MDX calculated members do not receive parameters. Instead, they pick up the context in which they're being run. In other words, they are intelligent enough to pick up that they are being run in the chart (i.e. "sliced") by month.
OK, so that covers the calculations for the chart - how about the analytic grid at the bottom? How are we able to show the selected product and its siblings, along with each parent product, all the way up to the top level? Well, we can do the following:
SELECT [MostRecent12Months] ON COLUMNS,
hierarchize(
union( ascendants( <<ProductSelection>>),
<<ProductSelection>>.Siblings ) ) ON ROWS
FROM [Sales]
WHERE [PctOfParent]
This is but one of many examples we cover in the MDX and PerformancePoint Server weeks of the SetFocus Business Intelligence Master's Program.
In the SetFocus Business Intelligence Master's Program, we cover the use of security roles in SSAS databases.
SQL Server Analysis Services 2005/2008 both allow you to define roles for when your Windows users (or groups of users) access OLAP data.
As an example, suppose in the AdventureWorks OLAP database, we wanted to associate users with a role that only allows visibility to data for the countries of Australia and the United Kingdom, for the product category of Bikes. You would create a new role in BIDS (or in SSMS), call the role something like TestBikeRoleForAustraliaAndUK, and make the following selections in the Dimension Data area for the Customer and Product Dimensions (next 2 screen shots):
You can test the role by going to the cube browser in BIDS - the cube browser provides the ability to view the contents of the cube as if you were a user associated with the new role of TestBikeRoleForAustraliaAndUK. But suppose you wanted to test the role in Excel? After all, many users prefer to use Excel to create Pivot Tables and Pivot Charts from OLAP data. But how can we open the cube in Excel and test the role, without needing to actually sign on as a user associated with that role?
It's actually quite easy. In Excel, you can create a connection to an Analysis Services database. You can open the connection properties in Excel, and set the Roles property in the connection string to the role you wish to test (see screen shot below).
After making the connection, you can access the PivotTable field list in Excel, and select the entire Customer Geography Hierarchy and Product Category Hierarchy (see screen shot below). However, since you're accessing the OLAP cube based on the Role in the connection string, you'll only see data for Bikes for Australia and the U.K. (screen show below that).
So as you can see, Excel provides us with the ability to test a role by placing the role in the Connection String property. Pretty slick!