OLAP cube design issue?

Question

Hi all,

I am a data analyst and having some trouble working with the cube produced by our IT department. I suspect it is designed less than optimally, but would like to hear other views before I go and talk to them.

We are a bank and our IT department has put together a cube describing account balances over various dimensions such as product, time, customer, and account ID.

They have the following dimensions:

dim_product

dim_time

dim_customer

dim_account

dim_activeflag

dim_zerobalance

I am having particular trouble with the dim_account dimension. In it are a few hierarchies: accountID, date_account_closed, date_account_opened etc.

The problem is, I need to utilise the date_account_closed value (by comparing it to the time dimension) in order to decide whether to sum certain balances or not (we want to ignore balances AFTER the account was closed). Ditto for the date_account_opened.

I have found it a real pain in the neck that the date_account_closed variable is in the account dimension - MDX throws up all sorts of errors because I am looking at individual accountIDs as well. I just wanted to see what people thought about the information for date_account_opened and date_account_closed belonging in the dim_account dimension when dim_activeflag and dim_zerobalance (which are also attributes which belong to each account) have their own dimensions.

How would people go about designing this cube? Because each accountID can have a date opened / date closed / active flag / zerobalance flag, should those bits of information go in its own dimension or the account dimension?

Thanks!

Answer 1

Couple of thoughts on this one. 

First, it sounds like the cube was designed with the active flag dimension so that you can easily filter on that to get only open account balances instead of having to do the open/close date filtering you mention.  Have you tried using the active flag in combination with filters on the time dimension to accomplish the filtering you need?  I would suggest trying that first.  If that works as expected, no additional development needed and you can continue your analysis without waiting for any dev changes.

Second, if the active flag doesn't give you what you need, you can essentially create 2 copies of your dim_time to relate to the opened and closed dates in dim_account.  You do this by creating a referenced dimension relationship from your measure group (fact table) to a role play of your time dimension indirectly through the opened and closed dates in the account dimension.

Sounds like you wouldn't be making this change, but I'll explain it here in case you can pass this along to your team.  You would do this by first defining a relationship in the DSV from your account dimension's opened date and closed date to the time dimension.  Then you'll create 2 role play copies of the time dimension specifically for opened and closed dates.  To do this, you'll define the two referenced dimensions in the dimension usage tab of the cube.  Add a relationship from the measure group to each of the the role-play time dims (opened and closed dates) with the account dimension as the intermediate dimension and the appropriate date as the intermediate dimension attribute to join across on.  This indirect relationship will allow the engine to navigate from the measure group out through account to the copy of the time dimension for the opened or closed date.  This will also give you a bit more flexibility around defining your desired date ranges for your filtering on open and closed dates.  

Here's a more detailed description of setting up referenced dimensions. 
http://technet.microsoft.com/en-us/library/ms166704.aspx

Hope that helps.

---

Brent Greenwood, MS, MCITP, CBIP
// Please mark correct answers and helpful posts //
http://brentgreenwood.blogspot.com

Answer 2

Thanks Brent! This really helped.