.png)
Analysis Services (DW)---a Technical Reference Guide for Designing Mission-Critical DW Solutions
|
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 Toolkit
1: 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.
-
Microsoft SQL Server 2008 Analysis Services Consolidation Best Practices
2: 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.
-
Running Microsoft SQL Server 2008 Analysis Services on Windows Server 2008 vs. Windows Server 2003 and Memory Preallocation: Lessons Learned
3: This article addresses specific memory management issues in conjunction with partitioned cubes for SSAS.
Analysis Services Distinct Count Optimization
-
Analysis Services Distinct Count Optimization
4: 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.
-
Analysis Services Distinct Count Optimization Using Solid State Devices
5: 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
-
Set up the design/development environment
Appendix
Following are the full URLs for the hyperlinked text:
1 The Microsoft Data Warehouse Toolkit http://www.rkimball.com/html/books.html
2 Microsoft SQL Server 2008 Analysis Services Consolidation Best Practices http://sqlcat.com/technicalnotes/archive/2010/02/08/microsoft-sql-server-2008-analysis-services-consolidation-best-practices.aspx
3 Running Microsoft SQL Server 2008 Analysis Services on Windows Server 2008 vs. Windows Server 2003 and Memory Preallocation: Lessons Learned http://sqlcat.com/technicalnotes/archive/2008/07/16/running-microsoft-sql-server-2008-analysis-services-on-windows-server-2008-vs-windows-server-2003-and-memory-preallocation-lessons-learned.aspx
4 Analysis Services Distinct Count Optimization http://sqlcat.com/whitepapers/archive/2008/04/17
5 Analysis Services Distinct Count Optimization Using Solid State Devices http://sqlcat.com/technicalnotes/archive/2010/09/20/analysis-services-distinct-count-optimization-using-solid-state-devices.aspx
6 Server Architecture (Analysis Services) http://msdn.microsoft.com/en-us/library/ms174776.aspx
7 Local Cubes (Analysis Services – Multidimensional Data) http://msdn.microsoft.com/en-us/library/bb522640.aspx
