Business Intelligence: Decaffeinated Please!

For many Database Administrators, Data Analysts and other IT and Business Professionals, Business Intelligence (BI) and Data Warehousing (DW) may be a new and uncharted territory with no clear path towards the destination. Others, who have already jumped head-on into the Business Intelligence journey, may be facing challenges that can potentially put their efforts at the risk of failure. For this reason I put together a presentation titled Business Intelligence: Decaffeinated Please!

In this presentation I cover terms and acronyms associated with Business Intelligence and Data Warehousing. I make a differentiation of what Business Intelligence is and what it is not. I also go over my “10 Rules of Wisdom” towards BI success based on lessons learned from personal experience as well as from insight gained from leading authors and speakers in the BI universe. The purpose of my 10 Rules of  Wisdom is to serve as a guideline for anyone involved in a BI initiaive or in its planning stages.

My “10 Rules of  Wisdom” for Business Intelligence Success are listed below:

1. The Business Intelligence solution needs strong executive management support. Keyword: Sponsor
2. The Business Intelligence solution must add value to the organization and be trustworthy.
3. Always have a Business Intelligence roadmap in clear view.
4. Define iterative success criteria.
5. Choose the right technology based on your business and user needs.
6. The BI solution needs to be understandable and documented.
7. The BI solution needs to be accessible.
8. The BI solution must be able to grow according to the business needs.
9. The BI solution needs to adapt to new business analysis needs.
10. Partner with experts in the field to help you achieve your goals.

These and other topics can be found on my PowerPoint presentation below which I continuosly enhance for my presentations for SQL Saturdays and User Groups.

 (click on image to download)

Slides updated 3/24/2011

Advertisement

Creating a Top 1, Top 10, Top n Customer or Product List using Named Sets in SSAS Part 1 of 3

Just like stored procedures in SQL Server, Analysis Services provide  a similar functionality through stored MDX expressions called named sets.  SQL server stored procedures allow you to:
– Return a set of data from your database
– Can be reutilized by being called in scripts
– Are named descriptive of the data they return (ideally)

Similarly, SSAS allows you to create reusable pieces of MDX code that can be called in other MDX scripts by its name or alias and have the same characteristics as described above. Additionally, these reusable pieces of MDX code may or may not use parameters just like parameter-less stored procedures.  In essence a Named Set is a predefined subset of your cube or a sub-cube. For purpose of simplicity I will go over named sets that do not use parameters.

One of the most common queries requested by Sales and Marketing Managers is a list of Top 10 Customers.

This list of customers may be required in ad-hoc queries as well as weekly, monthly, quarterly or yearly sales reports. This is where Named Sets in SSAS are most beneficial. Instead of rewriting the same MDX query over and over and having to change it everywhere you use it each time the criteria for Top 10 customers changes (believe me it will), you simply call this named set and whatever changes you make to the named set trickles down to wherever you call it.

The general MDX syntax to define this Named Set is:

CREATE SET CURRENTCUBE.[Top 10 Customers]
AS
TopCount
(
  (Existing [Customer]. [Customer].[Customer].Members]),
  10,
  [Measures].[Sales Amount]
);

As you can see the MDX function that helps us easily create this list is TopCount(). The keyword *Existing* forces the data set to be reevaluated each time dimension criteria changes, for example if different time period such as Year,  Quarter or Month is selected or if the cube is sliced by a particular product. In this case, the Top 10 Customers list will be generated based on the criteria applied.

Conversely if you need to create a list of the 10 least profitable customers or least profitable products for example, you can use the BottomCount() MDX function in a similar fashion.

Special attention needs to be taken when using BottomCount() MDX function for customers or products that had no correponding sales amount for the given criteria. For example if during last month 15 customers have no sales [Sales Amount]=0 or [Sales Amount] is NULL, then BottomCount() customers list will be made up of customers with $0 or NULL  sales amounts. These $0 and NULL sales amount records can be filtered out using the Filter() MDX function for $0 values and with the NONEMPTY() MDX function.

In Part 2 of this post I will be providing some examples of Named
Sets using the Filter(),  NonEmpty() and another useful function called Item() which allows you to select a particular tuple from a set.

In Part 3, I will be explaining the difference between TopCount() vs. Head() and BottomCount() vs Tail() MDX functions. In some instances a particular function might be simpler and more efficient.

Variance calculation using MDX calculated measure in SSAS

Actual vs. Expected Variance Analysis focuses on the difference between the projected or expected amounts and the actual amounts. These type of analysis are part of an important set of metrics for decision makers in all type of organizations and can be applied to Budgets, Production, Revenues, Profits, Efficiency and many other areas.

In SSAS OLAP cubes, these variances could be calculated through several ways. An easy and simple way to accomplish this is through calculated members using MDX in SSAS. There are several other ways to accomplish this calculation but for the scope of this post I chose a simple and quick way for beginner MDX’ers.

The Math
The basic formula for variance is:

Variance = Expected – Actual

The Requirements

You have been tasked to create an Actual Budget Variance cube in Analysis Services for the CEO of your company. In this cube the CEO is interested in viewing the Budget, Actual Budget, Expected Budget and Actual Budget Variance by Department and by Quarter.

Let’s assume we already have the Actual Budget Variance SSAS cube solution already created with a Department and a Quarter dimension as well as with the three Measures already loaded in our OLTP Data Warehouse (Budget, Budget Expected, Budget Actual):
(Click on image to expand)

We need to dynamically calculate the Actual Budget Variance as follows:

Actual Budget Variance = Budget Expected – Budget Actual

To create this calculation in SSAS follow these steps:

1) Open the Analysis Services Project Solution in Business Intelligence Development Studio (BIDS) and click on Calculations Tab and create a new calculated Member as follows:
(Click on image to expand)

2) Save the Solution and Process the cube.

3) Once processed, click on Browser Tab to browse  the cube. Expand the Measures group and you should see the new calculated member. Drag all measures and both dimensions into the browsing area. Group the Department Dimensions under the Quarter Dimension by moving Department column to the right. You can expand and collapse the Quarter Dimension to show/hide the details. It should look something like this:
(Click on image to expand)

From this example you can understand the basics of creating a calculated member using MDX. You can see the full script of the MDX calculated member by clicking on the Script View icon in toolbar under Calculation.
(Click on image to expand)