SSWUG vConf – Analysis Services End-to-End

Presenter: Craig Utley
craig@solidq.com

DB –> SSIS –> Relational Data Warehouse –> SSAS (Cubes) –> SSRS

Data Sources

• SSAS Projects have one or more data sources that point to data in some physical format
• Data sources should impersonate a domain use with least privileges for the purpose of accessing data.

Data Source Views

• Are required as they are the only things cubes and dimensions can see
• DSV’s are logical representation of the schema for the dimensions and cubes
• You can create new logical primary keys, logical columns, and logical tables in a DSV
• Remember DSV’s are logical so your source data is not altered.

Demo Steps (Fairly Standard Project Creation Steps)

• Create New Project
• Create a new data source (Windows authentication, use specific domain account)
• Create new Data Source View
• Setup Named Calculations where needed
• Start Creating Dimensions
  1. Attribute Relationships
  2. KeyColumns
  3. NameColumns
• Create the Cube
  1. Cube has one or more Measures Groups à Measures
  2. Dimension Usage tab shows what dimensions are used for what measure groups.
  3. Create the aggregations
     • Aggregation usage (Typically use Default or Unrestricted)
       • Full – Must use this attribute
       • None – Never use this attribute
       • Unrestricted – Consider this attribute regardless but only use it if applicable.
     • Use 20% for the Set Aggregation Options as a starting point
     • Save it / Deploy later
• Build
• Deploy
• Processing (BIDS does all 3 by default)

Fact: Can use BIDS 2008 to target a 2005 version project

Creating a Dimension

1. Attribute Relationships
   1. Attribute Relationships are the most important step after creating multilevel hierarchies is to create attribute relationships wherever possible.
   2. Attribute relationships tell SSAS how attributes are related so itacan perform more efficient aggregations and queries.
2. KeyColumns and NameColumns
   1. Example: 4 quarters in a year, need to add year to the key to make each year/qtr unique.

All Dimensions must have a KeyColumn.

Attribute Relationship designer in 2008 looks good. Drag the child onto the parent to create the relationship structure. You can have multiple paths through the attribute relationships.

Set AttributeHierarchyVisible property to false if the attribute is also used in a multi-level hierarchy.

Set format strings in the cube measures

Set IgnoreUnrelatedDimensions to false – Fixes issue with repeating amounts when attribute doesn’t make sense.

Popularity: 1% [?]