Presenter: Craig Utley, Mentor with Solid Quality Mentors
craig@solidq.com
http://learnmicrosoftbi.com
General Design
- SSAS 2005 and 2008 are extremely different from SSAS 2000
- Upgrading from 200 might work on small simple cubes. Not recommended
- In almost all cases, expect to redesign the AS db to take advantage of new features
- For larger project, plan to have a hardware expert available who understands SAN, load balancing, etc…
Design – Data Sources and Data source Views
- DS should us Windows Authentication when connecting to SQL server and then impersonate a domain account created for this purpose
- Account should have the least privileges possible to get to the data
- For SQL Server use the Microsoft OLE DB Provider for SQL Server or the SQL Server Native Client instead of the .NET Data provider
- In the Data Source View, create a new diagram for each fact table.
- This helps make each view look like its own star or snowflake schema
- For simple calculations and concatenations, create new named calculations in the DSV
Design – Dimensions
- There are a number of design issues related to dimensions, including
- Attribute relationships
- Attribute key columns and name column
- Multilevel hierarchies and attribute hierarchies
- Other areas
Attribute Relationships
- Attribute relationships tell the Analysis Services engine how attributed are related so that it can build aggregations and speed queries (First thing you need to do)
- Ensure that attribute relationships are created and created correctly.
- The visual designer in BIDS 2008 makes this much easier
- Set the RelationshipType to Rigid when possible.
Attribute KeyColumns and NameColumn
- Ensure that the KeyColumns is set to identify unique values
- For example, a month value of 1 is insufficient if the dimension contains more than a single year
- Set the NameColumn to a column that makes it clear what is being shown
- For example, instead of just a month value of 1, combine the month and year
Other Dimension Design Issues
- Hide attribute hierarchies for attributes added to multilevel hierarchy
- A dimension should contain no more than one non-aggregatable attribute
- Use numeric key columns
- Especially important for dimensions with more than 500,000 members
- Parent-child dimensions should not have more thank 500,000 members in the key attribute.
- Specify a dimension as type Time when possible.
- Needed for some functions to work.
Design – Measure Groups
- Avoid having multiple measure groups with the same granularity and dimensionality
- Set IgnoreUnrelatedDimensions to False when possible to avoid repeating a value for all unrelated members!
- Big pitfall for those new to Cubes.
- Don’t create a many-to-many relationship if the dimensions or intermediate fact table have more than one million members
- Alternatives exist, check out Erik Veerman’s blog at http://blogs.solidq.com/EN/Erik
Design – Aggregations
- Rule of thumb. Start with 20% using aggregation wizard.
- Then log queries during periods of normal activity and use the Usage Based Optimization wizard (UBO) to define better aggregations.
- 2005 was a bit lacking, fixed in 2008
- Set approximate counts if there is too much data to let the wizard count the rows
- When creating custom aggregations, do not create aggregations below the measure group’s granularity
- Don’t create overlapping aggregations
Funny. He said “Agg”. Of course to me that means “Agriculture” not “Aggregations”. Ha!
So, wonder if they are going to give away some of those button down SSWUG.org shirts. Very nice! 
Design – Partitions (Ugh)
- Enterprise edition only feature.
- Create partitions for larger data sets in order to speed up queries.
- Partition by common usage, such as time
- Current year, vs. historical
- Partitioning by more than one criteria is acceptable, but ensure fact records do not appear in more than one partition
- Cubes should have no more than 2,000 partitions with a max of 20,000,000 records each (2000 only)
- Partition the relational warehouse fact table the same way as the cube.
Processing
- Tweak the number of parallel tasks on the production server to see if a different value decreases processing time
- Use ProcessData and PRocessIndex instead of ProcessFull to get more consistent processing pattern
- 3-5% slower but recommended
- Use ASCMD to manipulate and process objects, especially on very large databases
- Use the <Parallel></Parallel> block to process objects in parallel
Server Settings
- If running other apps or multiple instances of SSAS
- Move Memory/LowMemoryLimit below 75%
- Move Memory/TotalMemoryLimit below 80%
- Modify CoordinatorExecutionMode to increase parallelism
- Negative numbers ……******************8
Querying Tips
- Learn how to monitor queries in action
- See Identifying and Resolving MDX Query Performance Bottlenecks in SQL Server 2005 Analysis Services
- Make sure to understand the ThreadPool/Query settings
- See if data is coming from a cache, an aggregation, or a subcube query
- In high usage scenarios it can be useful to adjust MemoryHeapType
- Call PSS for support
- Learn how to scale up and scale out
MDX Tips
- Set Non-Empty behavior property on calculations when possible.
- Be careful can degrade performance
- Explicitly reference cells when possible
- Move simple calculations from calculations to the DSV
- Learn how to use the Scope statement.
- Makes for easier to read queries where you typically would have lots of IIF.
Summary
- There are many best practices that must be applied during the design phase.
- Dimensions
- Measure Groups
- Data sources and such
- Processing can be tweaked through parallelism and memory adjustments
- Querying can be tuned through proper MDX, memory adjustments and partitioning
Thanks to @swynk for the reminder that I can download the slides. So, my next posts should be just notes of things not on the slides. Whew! I can slow down.
Blessings.
Popularity: 8% [?]
