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)

Lessons from my first week on Twitter.

I finally gave in to the peer pressure of social media and started tweeting and following fellow twiterers in the SQL/BI arena. The first set of questions that popped-up on my head when I made the decision to start my Twitter account were:

1. What am I going to tweet about?
2. Who will care about what I got to say?
3. Will following too many people cause an information overload?
4. Should I limit my tweets to SQL/BI only matters?
5. Will I be lost with all the lingo and abbreviations as in SMS texting?
6. Will I become addicted once again to something non-productive?
7. If I have survived life so far without Twitter, why do I need it now?
8. If I complain about not having enough time balancing work, family and professional development, how can I justify twittering?

Although,  haven’t been able to answer all of these questions, I decided to bite the bullet and get on Twitter. The closing case to become part of the Twitter gang was  made by Jorge Segarra @SQLChicken during a SQL Server R2 / Sharepoint 2010 presentation during the April 5th Tampa Bay SQL BI User Group meeting. Jorge’s statement about being a “legitimate business and professional tool for DBA’s” was heard by my boss as well, who I had invited to attend this particular meeting. Now I can justify my tweetering and blogging at work. Yey!

My next challenge was to justify my twittering at home. Of course, I wear the pants at home but…it is my wife who choses which ones should I wear, when and where. Her resistance was as expected and she just made me realize that I have become what I have always criticized her young sister about…a texting junkie! OK, so less twittering at home I concede. See what I talk about deciding when and where? She just knows how to keep the family harmony.

Eighty (80) tweets later within a week, tweeting  as @sqljoe , I have learned a couple of things:

1. Twitter feels much like those old IRC chats in  which you can filter messages by sender and topic (#channels).
2. Twitter is a great way to get in touch with respected SQL/BI experts such as seasoned DBAs, developers, authors and folks from Microsoft.
3. Interesting to learn what other DBA’s are doing in their day to day tasks, challenges and their approach, as well as learning about the technologies they play with at their jobs.
4. Twitterers and bloggers are an important part of technology marketing strategies to get the word out quickly about new products and features with minimum effort and cost, other than fine wine and limos (#sqlr2 Microsoft tweetup).
5. There are SQL chefs, midnighters, chickens, sheeps, chicks, bucks,  knights, doctors, m-a-chanics, soldiers, runners and of course regular joes and green handed masters.
6. SQL Twitterers use the word Pimpin a lot.
7. Body disposal services can be contracted through Twitter.
8. SQL experts have hair feuds as well.
9. Margaritas make you enjoy progress bars way too much.
10. DBAs do have lives and are a good source of recommendations for places to eat.

 Among other things,I think my first week on Twitter was with lack of better terms:  interestingly fun, cool and informative.