Contain DW/BI Scope Creep and Avoid Scope Theft

Nice article at Kimball Group Site, exposing the need to keep your Business Intelligence Program on track – avoid losing your inicial Scope!!

http://www.kimballgroup.com/2013/04/01/design-tip-154-contain-dwbi-scope-creep-and-avoid-scope-theft/

My personal Tips

1 – Achieving a balance between user freedom and scalable design will determine the succes of a cube”.

Cube Testing

1. Make sure that the high level totals are correct. Test double counting. Randomly sample the “leaf level data”, comparing it with the operational system. Accuracy is the most important thing in cube testing.
2. Give your cube to 3 key users for functionality testing. They understand the business more than you, and it’s them who will be using the cube, not you. Choose users who are familiar with both the BI tool and the data.
3. To test the query performance, submit complex queries with sub totals, with many attributes in rows/columns/background. Warm cache. Obtain execution duration using Profiler. Consider a) tailoring the aggregations to improve slow queries, b) having snapshot fact tables in DW to support cube performance, c) moving the large attributes into the same dimension.
4. Stress test using VS Team Suite. It simulates many users querying the cubes simultaneously.  Lower cost alternative is to create an ADOMD app that submits MDX queries.
5. For BAU change requests, e.g. additional attributes, compare the changed cube in UAT with the Prod cube. Verify that they produce the same high level numbers.
6. Test processing the cube and time it. If you use a scheduler software use it in testing. If the cube processing is monitored, check that the alert is working. If you do dimension process update + cube process default, check that a) you don’t miss any dimension, b) no issue with collation (upper/lower case) when the data is updated, and c) query performance is good. Using incremental processing could half the processing time.
7. If you have proactive cache, test the response lead time by changing the underlying source data that is being monitored. If it’s schedule polling the lead time should match the polling interval.
8. Test the calculated measures, named sets and scoping assignments by random sampling of a few cases with different data nature. Compare with manual calculation.
9. Test the actions (URLs, reports, drillthrough). Make sure they open the correct URL/reports and provide the correct drillthrough numbers. Also test KPIs.
10. Test the security. Use white box method. On SSMS, access the cube as different users, check if they can only see the dimensions and members they are allowed to see. Also test from the BI tool, using a test domain account, included as a member of the cube role.