December 7, 2011 at 11:29 am
I am building a new dimensional data warehouse for the financial institution I work for and I need to include goals for various metrics such as 'Percent of Accounts With a Checking Share', 'Average Services Per Member', etc. The goals would be by branch location and time period. In some cases the goal may be the same for every branch and in other cases it may be different for each branch. I already have a dimension table for the branch information. The design needs to be flexible so I can easily add new metrics and eventually include metrics for other business areas (lending, marketing, etc.). I am looking for some suggestions on where the metrics and goals should be in my design. Should I create a new dimension table which stores all of the possible metrics and then have another table with the actual goal for each branch? How have you handled different types of goal values (percent, dollar amount, integer value)?
Any suggestions would be greatly appreciated.
December 8, 2011 at 11:57 am
Derek Markel (12/7/2011)
I am building a new dimensional data warehouse for the financial institution I work for and I need to include goals for various metrics such as 'Percent of Accounts With a Checking Share', 'Average Services Per Member', etc. The goals would be by branch location and time period. In some cases the goal may be the same for every branch and in other cases it may be different for each branch. I already have a dimension table for the branch information. The design needs to be flexible so I can easily add new metrics and eventually include metrics for other business areas (lending, marketing, etc.). I am looking for some suggestions on where the metrics and goals should be in my design. Should I create a new dimension table which stores all of the possible metrics and then have another table with the actual goal for each branch? How have you handled different types of goal values (percent, dollar amount, integer value)?Any suggestions would be greatly appreciated.
It all depends of the complexity of your model and business requirements.
I would probably implement goals as a separate datamart alike structure shared by the actual datamarts handled by the dwh.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.December 9, 2011 at 5:50 am
From what I've read, data warehouses for financial institutions can be extremely complicated. So be careful. If you're a beginner, you may need outside help.
To address of couple of your points: assuming the level of granularity for the cube is account, you can add a tiny int field that is 0 or 1 to indicate whether the account has that property or not.
As for goals, you will likely find that the goal level of granularity does not match that of the other cube(s) and will have to be on it's own with the actual results summed from other cubes. It's supposed to be possible to join cubes for just this purpose, but for all my experience with SSAS, I have been unsuccesful in doing this in a user friendly way.
January 5, 2012 at 3:26 pm
I'd let the end user tool handle it vs in the data warehouse. There are plenty of tools that can add KPI.
January 20, 2012 at 10:19 am
Not an expert advice 🙂
How about a simple star schema with three dimensions: Calendar, Branch, Metrics, and fact table called FactGoals.
Measure in the fact table can be GoalValue.
Add an additional column for the Metrics dimension, let's say ValueFormat. This will determine if the metric is stored as a integer or percent.
You can store GoalValue in the fact table as integers always, but when reading this has to be formatted based on the format defined in the metrics dimension.
Again, just a thought. I haven't implemented this.
Sam Vanga
http://SamuelVanga.com
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply