I just wanted to pass along a few topics from my Business Intelligence In-A-Day class from last week. After each class, I would like to post some of the topics that were discussed more than others. First, I would like to thank all the students that attended the class at SharePointRx. Here are the major touch points;
1. When loading a dimensional, each attribute determines the SCD Type. A dimension could have several Types. The SSIS example I showed is when a Person changes territory, and the business team wants all the historical fact (sales) to be carried-over to the new territory, which is Type1. Basically preserving the surrogate, but updating the person record's Territory. Then I showed an example of a Type2 in the same person dimension. The scenario is simple, what happens when the user wants to show the person's purchasing behavior before they were married, and wanted to track the sales after the change of marital status. This is a real case, as direct mailing would send out different coupons and offers to the new Person's address. Basically when the last name changes, as well as the marital status, we keep the old surrogate key, and insert another record, creating another surrogate key for later facts to be associated with. I am not a big fan of designing dimensions with multiple subject areas, but grouping persons in a location lends itself to this technique. Also, don't use the term Type1, Type2, or Type 55 when discussing the changing behavior, because it really depends on how the business team wants to track history. Kimball's SCD types are a great resource for the Tech team, but really the requirements from the functional teams will dictate which SCD type. I know there is not a Type55, but it really does not matter how many types surface, it is up to the business team and how they want to handle history.
2. Use the right reporting tool in the Microsoft Stack to suit the needs of the business. You would not want to use PerformancePoint Services 2007 if the user wanted a pie chart. They don't exist in 2007, but in 2010 they do. Use SSRS, Excel Services, or ProClarity. Use SSRS for subscriptions and a Formal design for reports and details. Use Excel Services if you want to interact directly with SSAS, and not any "other" data source. Use ProClarity in a limited role since Microsoft is pretty much decommissioning the product, and will stay 32 bit. I don't know why they are, as most of my clients prefer ProClarity over any of the other visualization reporting.
3. Design your SSAS cube with the Business Team in mind, and not IT. Get the Functional Teams involved early and often. Change the names of the "Dim" and "Facts", or any other database physical name to a friendly one. The use of views may be appropriate as other reports can use the same names. Use Perspectives to group subject areas, and not for security.
4. Use PerformancePoint 2007 Scorecards as an "enabler" to the Companies’ Enterprise Performance Management Methodology, and not for a report across all businesses. The reason is obvious, most departments in the organization have different performance metric, even down to the line workers.
5. Use Shared (conformed) Dimensions for all facts, even for aggregated tables when possible.
This was a fantastic class, and I really enjoyed everyone's passion to learn more regarding the Microsoft Business Intelligence Technology Stack.