Analysis Services (DW)---a Technical Reference Guide for Designing Mission-Critical DW Solutions

Article
03/06/2014

Want more guides like this one? Go to Technical Reference Guides for Designing Mission-Critical Solutions.

Microsoft SQL Server often serves as the source of both transactional (OLTP) data as well as pre-built data warehouse data. Key distinguishing features primarily focus on the connectivity to SQL Server as well as the geographical proximity of the SQL Server source to the Data Warehouse target. Other important items include volume, frequency of extract/load, complexity of data transformation, access windows to extract data.

Best Practices

The following resources provide reference material and additional information.

The Microsoft Data Warehouse Toolkit1: This is an excellent book encompassing all aspects of implementing data warehouses and data marts using the entire Microsoft Suite of tools. Refer to Chapters 7, 8, and 10 for basic and advances implementation of SSAS.
SQLCAT's Guide to BI and Analytics2: Many customers have deployed Analysis Services cubes in a "helter-skelter" manner and should follow a best practice for bringing the cubes into a more homogeneous/managed environment. This article provides guidelines for a holistic consolidation including for SQL Server/Hardware/SSAS.
SQLCAT's Guide to BI and Analytics3: This article addresses specific memory management issues in conjunction with partitioned cubes for SSAS.

Analysis Services Distinct Count Optimization

SQLCAT's Guide to BI and Analytics4: Distinct count (such as unique visitor counts on a web site) calculations provide valuable information but come with a number of performance challenges. This white paper describes tests that were performed to determine how best to optimize these calculations and includes best practices based on the test results.
SQLCAT's Guide to BI and Analytics5: To expand on the distinct count optimization techniques provided in the Analysis Services Distinct Count Optimization Using Solid State Devices white paper, this technical note shows how using solid state devices (SSDs) can improve distinct count measures.

Case Studies and References

The following provide helpful information:

Questions and Considerations

Following are some question and consideration you can use when working with customers.

Analysis Services delivers much faster query performance than a relational system, but the cube(s) must be loaded and calculated.
Carefully consider data volumes.
Are there any Data Mining requirements? This is a powerful feature and often not utilized.
SSAS provides a more convenient platform to deliver semi-additive facts than stand SQL queries.
SSAS is not a substitute for a solid DW design/implementation. It will only augment the DW, not replace it.
Clearly define and communicate what SSAS delivers, and what it doesn’t deliver.
Determine if MOLAP, HOLAP, or ROLAP apply.
Seven easy steps:
- Set up the design/development environment
- Create a Data Source view (of the underlying DW)
- Create and fine-tune your dimensions
- Hierarchies
- Derived Attributes
- Many-to-many dimensions
- Run the Cube Wizard and edit the resulting cube (much faster than starting from scratch)
- Deploy the database to your development server
- Create calculations and other "decorations"
- Iterate, iterate, iterate