Lab 9

1. Create a SSAS project

   • Data Sources

   • Data Source Views

     Data Source Views helps to choose which table should participate in the SSAS project. It also helps you decouple physical database structure from SSAS database structure.

   • Cubes

     Cubes defines measures and dimensions for your data. You can do calculations in Cubes.

     Data of SSAS database and RDBMS database is out of synch. So you need to process data firstly.

     You can see the data in Browser.

     MDX is the Query language for SSAS Cube storage.

Lab 10

1. Time Series

   Create a time dimension table and connect it to your fact table.

2. Dimension Usage

   Dimension usage defines the relationships between a cube dimension and the measure groups in a cube. When you add a database dimension or measure group to a cube, SSAS tries to determine dimension usage by examining relationships between the dimension tables and fact tables in the cube's data source view, and by examining the relationships between attributes in dimensions.

3. Connect to Excel

   Only developer can use Browser to build report. In order to show results to customers it's better to import SSAS database into Excel.

Lab 14

1. Hierarchical Dimensions

   You need to build hierarchies before analyzing data. You can use self-join to create hierarchies. After that, remember to design hierarchies in Dimension Structure.

Lab 17

1. KPI

   A KPI (Key Performance Indicator), in a tabular model, is used to gauge performance of a value, defined by a Base measure, against a Target value, also defined by a measure or by an absolute value.

   Example

   • Value Expression
     [Measures].[Customer Amount]

   • Goal Expression
     [Measures].[Customer Amount] + 1000

   • Status

     Case
         when [Measures].[Customer Amount] > 1000
           then 1
         when [Measures].[Customer Amount] < 1000
           then -1
         else 0
     End
     

   • Trend

     Case
         when (([Time].[Year].[prevmember], [Measures].[Customer Amount]) < [Measures].[Customer Amount])
           then 1
         when (([Time].[Year].[prevmember], [Measures].[Customer Amount]) > [Measures].[Customer Amount])
           then -1
         else 0
     End
     

   • Results

     KPI

Resources

• Create KPI in a SSAS Cube
• Adding a KPI to an SSAS Cube
• Display of SSAS KPI Indicators in Excel

Lab 19

1. Calculations

   A calculation is a Multidimensional Expressions (MDX) expression or script that is used to define a calculated member, a named set, or a scoped assignment in a cube in Microsoft SQL Server Analysis Services.

   • Calculated Members

     Calculated members are members of a dimension or a measure group that are defined based on a combination of cube data, arithmetic operators, numbers, and functions.

   • Named Sets

     A named set is a CREATE SET MDX statement expression that returns a set.

   • Script Commands

     A script command is an MDX script, included as part of the definition of the cube. Script commands let you perform almost any action that is supported by MDX on a cube, such as scoping a calculation to apply to only part of the cube.

Resources

• Calculations
• Defining Calculated Members

Lab 21

1. Reference

   A referenced dimension relationship allows us to create a relationship between a measure group and a dimension that is indirectly related to the measure group using an intermediate dimension.

   Reference

2. Materialize

   When we check the materialize option, the link between the referenced dimension and the facts is materialized. This leads to slower cube processing but better cube query performance.

3. Many-to-Many Relationship

   In Analysis Services, you define a many-to-many relationship between a dimension and a measure group by specifying an intermediate fact table that is joined to the dimension table.

   Many-to-Many

Resources

• GOTCHAS WITH REFERENCED DIMENSIONS
• Introduction to Many-to-Many Relationships in SSAS