EXTENDING SSAS CUBES

1. )Define attribute hierarchies and attribute relationship to optimize cube design and facilitate data analytics.
2.) Enrich the reporting experience by building a end user layer consisting of KPI’s, action, translations and perspectives.
3.) Use MDX language expressions to define important business metrics not available in fact tables and require custom expressions.

A.) Defining user hierarchies and dimension relationships:
Attribute Relationship:
Notes:
1.)If a dimension is based on Star Schema its source is a single dimension table. Snowflake schemas have multiple dimension tables.
2.)Unlike relational database which is a 2 dimensional structure of rows and columns, a UDM is a multi-dimensional which supports data hierarchies eg Year, Quarter and month levels and the relationships that exists between these attributes.
All dimension attributes are directly or indirectly related to the dimension key attribute with 1:1 or M:1 logical relationships.
By default the UDM is not aware of the logical relationships among attributes. It defies M:1 relationship with all attributes and the dimension key, except with snowflake dimension where it automatically creates separate M:1 relationships among attributes from different tables in the attribute relationship tab in the dimensions.
One must spend time to understand and appropriately define attribute relationships.
3.)Setting up attribute relationships provide the following benefits:
-- Server can optimize the storage of the attribute hierarchies
--SSAS can effectively retrieve and aggregate data.
If there is no attribute relationship the query that groups data by product category will scan the fact data on the fly to summaries the individual products but if the attributes are related the server could optimize the query and use the subcategory subtotals.

Attribute Relationship Properties:
1.) Attribute- Specifies the related attribute name
2.) Cardinality- Cardinality of relationship definition
3.) Name- Relationship name specification
4.) RelationshipType –Flexible (slowly changing dimension) or rigid (does not change with time)
5.) Visible- If False the corresponding member property will not show to the end user.

Creating a New attribute Relationship or creating a new Dimension hierarchy:
1.) Attribute Relationship: Right click the source attribute in the attributes pane (in the Attribute relationship tab for a particular dimension) that is on the one side of the relationship. Make sure the source attribute shows the attribute on the many side and the Related Attribute is the attribute on the one side of the relationship.(Territory CountryTerritory Group). Else drag and drop the many side attribute to the one side attribute in the Attribute relationship pane.
2.) Dimension Hierarchy: Right click the attribute you want to be at the top level and say ‘Start New Hierarchy. Else drag and drop the top level attribute to the Hierarchy tab in the dimension structure.
Important
Note:
1.)Although you can set up the user defined hierarchies without defining the attribute relationships, this should be avoided because it reflect poor design.
2.)Consider hiding attributes that are used in user hierarchies by setting their AttributeHierarchyVisible property to False to prevent redundancy in user exp.

Associating Dimension to User Group:
When a dimension is added the cube designer determines which measure group the dimension is related to by using the table relationship metadata within the DSV on which the cube is based.
The dimension usage tab within the cube designer defines the various relationships that should exists between each measure and dimension within the cube.

Relationship Types:
1.) Regular Dimension- is joined directly to the fact table through a granularity attribute
2.) Fact- When the dimension is based on the fact table used to define the measure group
Eg To see all sales order line items for a given Sales Order, you need to build dimensions directly from the fact table and then join them to the related measure group through a fact relationship.
3.) Referenced- Given dimension is related to fact table through an intermediate dimension.
4.) Many-to-Many- Dimension is related to a given measure group through a intermediate measure group
5.) Data Mining- Defines the relationship between a dimension based on a data mining model and a given measure group.

B.) Creating KPIS's, Actions, Translations and Perspectives:
B1.) KPI:
Organisations use KPI's to gauge their business performance. Eg Sales Profit, Revenue Growth, and growth in Customer base. It shows the health of a company.
SSAS is suitable for KPIs because
1.) Automatic calculation of KPIs- The server automatically calculates the KPI properties as the user slices the cube data. eg a user can drill down to time dimension and cube could calculate the KPI across the hierarchy of year, quarters and month levels.
2.) Time effective-Since the dashboards include different chart and report views that show historical trends, the server is pinged with many queries including large data sets and the page is rendered within seconds.

Expression Based Properties of KPI:
1.)Value
Current Value of the KPI
2.)Goal
What the KPI value should be in a perfect world
Following exp sets it to 40% more than Reseller sales Amount from the previous date period.
1.40*([Date].[Calendar].PrevMember ,[Measures].[Reseller Sales Amount])
The PrevMember function returns the previous member in the hierarchy eg if the level is yr it returns the previous year.
3.)Status-
Indicates how the KPI value is as compared to Goal.

1-Good performance-->
When KPIValue(Reseller Sales")/KPIGoal("Reseller Sales)>=1

0-Acceptable Performance-->
When KPIValue(Reseller Sales")/KPIGoal("Reseller Sales)<1 and When KPIValue(Reseller Sales")/KPIGoal("Reseller Sales)>=.85

-1-Under performance-->
Else the above statements

SSAS provides several standard KPI related functions:
--KPIValue(to retrieve KPI values)
--KPIGoal(to retrieve KPI Goal)
--KPIStatus(to retrieve KPI Status)
--KPITrend(to retrieve KPI Trend)

4.)Trend-
How the KPI is doing over time. Returns -1 to 1. It is equal to the following expression.
(
[Measures].[Reseller Sales Amount]-([Date].[Calender].PrevMember,[Measures].[Reseller Sales Amount])
) /
[Measures].[Reseller Sales Amount]

Additional KPI Properties:
Associated Measure Group
Display folder
Parent KPI- Used to indicate the parent KPI of the current KPI. Useful if the set of KPIs are shown together as they might be in a scorecard.
Current Time Member
Weight
Status Indicator and Trend Indicator- used to indicate the images the client should use to display the values graphically.

Note:
You can use KPI aware client applications such as Excel and SSRS to display KPIs on the report.You can also write MDX statements to directly query the KPI properties by using KPIValue(), KPIGoal(), KPIStatus() and KPITrend()
Eg below statement retries the properties of the Reseller Sales KPI that ave been implemented in the cube sliced by the Product Category by the year 2003....

SELECT { KPIValue('Reseller Sales"),
KPIGoal('Reseller Sales"),
KPIStatus('Reseller Sales"),
KPITrend('Reseller Sales") } ON COLUMNS,
[Dim Product].[Product Category].Members ON ROW
FROM [Adventure Works]
WHERE [Date].[Calender].[Calendar Year].&[2003]

B2.) Implementing Actions:
Actions provide another mechanism by which the users can gather information and take next steps based on the information they find in cubes
Actions can extend the cube in a versatile way eg if a user has drilled down to the lowest level of a dimension eg product dimension and wants to see the individual sales and orders placed for that product. If this info is not stored in a cube one can implement a reporting action that lets the user use a reporting action that displays a report with the required data.

Note:
Actions are defined in a cube but interpreted and initiated in a client app. Not all clients support actions. eg excel 2007 supports actions but SSRS does not. An action relates to an object(eg dimension table particular cell) in the cube.

Action Types:
Define action types to integrate the cube with client applications. They inform the client applications how to interpret the action.
1.)Regular Action Type
--Dataset-action content is a MDX statement
--Proprietary- action is client specific. Client is responsible for interpreting the semantic meaning of action.
--Rowset-Action content is a command statement. Unlike dataset, a rowset action targets any OLE-DB compliant data source, including relational db
--Statement-Content type is an OLE-DB command. The statement should not yeild any result except success or failure
--URL(default)- action content is a URL
a.)Used to construct a URL that displays a reporting server report
b.)Used to build a URL that references a specific page on the corporate website related to the product.
2.)Drillthrough Actions:
Lets the client request details behind aggregated cell values
3.)Reporting Actions- Can be used to request SSRS reports.

Properties:
1.)Name
2.)Target-
1.) target type(attribute member)- If you set cube the action will relate to the entire cube and do not need to provide more details about the cube. In the client the action is available for all cube objects every member , every cell, every dimension and every level.
Limit the action by selecting Dimension members. The action in this case is available for all members of the dimension specified in the target object.
Select Hierarchy or level to limit the availability of the action to the members of the specific hierarchy.
Another option is to select cell as the target type. when a user clicks a cell in a client application the action passes the value of teh cell as the parameter.
2.)target object(attribute hierarchy) if want to target a attribute hierarchy
3.)Condition

Note:
For configuring a drilldown action, one needs a list of drillthrough columns that the end user will see when the action is executed.

B3.) Localisation of Cube through Translations:
Translations support International Users.
To translate the cube metadata including captions of cubes, cube dimensions, measure group, measures, KPI, actions, named sets browse to the translation tab in the cube designer.

B4.) Implementing Cube Perspective:
Represents a subset of the cube.
Main purpose is to reduce the perceived complexity of the large cube by exposing only a subset of the cube objects.
eg if the members of a project are interested in browsing just the reseller sales, a perspective can be created that includes only the reseller sales measure group and the associated dimensions.
By default the cube has a single perspective that exposes the entire cube content.
Perspectives cannot be used to enforce restricted access to portions of the cube(security mechanism).

C.)Creating Calculations and Queries by using MDX
MDX gives the programming power to implement custom solutions.

MDX Syntax:
MDX used in 2 main ways:
1.)To query
2.)To extend multidimensional cubes- eg implement business metrics eg KPI's

Tuples:
Multidimensional coordinate that identifies a single cell in the cube space.
eg [Product].[Product].[Road Bike],[Date].[Year].2005,[Measures].[Internet Sales] references a cell found at the intersection of product Road Bike, year 2004 and measures Internet Sales.
Sets:
Collection of tuples with same dimensionality or attribute hierarchies. represented by {}
eg
1.){[Sales Territories].[Countries].[All Countries],[Date].[Year].[2004],[Product].[Product].[Mountain-100]}
2.){[Sales Territories].[Countries].[All Countries],[Date].[Year].[2004],[Product].[Product].[Road-200]}

Notes:
1.) The cube measures are treated as members of a special Measures dimension.
2.) When a dimension member in a tuple is referenced, you can use a member name eg [Product].[Product].[Mountain-100]. This syntax works only if there is a single product with that name.
You can resolve the member uniquely by using its key and prefix the key by & eg [product].[Product].&[10] will resolve the member uniquely even if there are more than 1 same name members.
3.) Cube space consists of many hierarchies, enumerating them is tedious so if the attribute hierarchy is missed it references to the default hierarchy...Usually the
"All Member" hierarchy.
4.) Can use the CurrentMember for most dimensions, but you do not need to explicitly reference in the tuple By default the Current Memeber of each attribute hierarchy is implicitly referenced in a tuple.
5.) Difference between 'Members' and 'All'-
Eg [Product].[Category].[All] Dragging the All Member of any hierarchy gives all the members present in that hierarchy. eg it returns just 1 row with All data.
Eg Product.Category.Members Members lists the the members from the category attribute hierarchy of the Product Dimension



Basic MDX query:
SELECT [Measures].[Internet Sales Amount] on COLUMNS
[Product].[Category]. ON ROWS
FROM [Adventure Works]
WHERE [Date].[Calendar Year].&[2003]

Notes:
1.) A query can have upto 128 axes(numbered 0 to 127)
First 5 are named COLUMNS, ROWS, PAGES, SECTIONS and CHAPTERS.
2.) Cannot skip axes

*Members-returns all members of the hierarchy

MDX Functions for Navigating Hierarchies:
1.)CurrentMember-eg [Order Date].[Calander].CurrentMember, [Measures].[Reseller Sales Amount] tuple returns the reseller Sales Amount for the member of the calendar hierarchy that references the current cell.
2.)PrevMember-eg [Order Date].[Calander].PrevMember returns the previous member with respect to the current member in the calendar hierarchy
Imp: Used to define KPI Trend and KPI Goals.
eg KPI Goal= 1.40([Date].[Calendar].PrevMember,[Measures].[Reseller Sales Amount])
3.)Children-eg [Order Date].[Calander].[2003].Children returns all quarters of the year 2003 assuming that the level below the calender year in the calender hierarchy is the quarters.
4.)Parent- Returns the member's parent in a given hierarchy
eg [Product].[Category].[Product].[Road Bike].Parents returns th Bike members of the Product Category Hierarchy.
5.)Member-Returns all the members of the hierarchy eg [Product].[Product].[Product]. Members return all the products of the product dimension and product Hierarchy excluding the All member.

Functions for Navigating Time: These functions help navigate time dimension.
1.) Lag
eg [Order Date].[Calendar].Lag(1) returns the previous sibling of the current year.
So if the current member is quarter level the Lag(1) will give the previous quarter
2.) Parallel Period- Returns the member from the prior period in the same relative position as the specified member with an optional offset.
If no member is specified then 0 is assumed.
eg 1 with 0 offset
Parallel Period([Date].[Calendar].[Calendar Quarter], [Date].[Calendar].[Month Name].[October 2003]) returns October 2002.

Syntax:
ParallelPeriod( [ Level_Expression [ ,Index [ , Member_Expression ] ] ] )

eg 2 with offset:
SELECT ParallelPeriod ([Date].[Calendar].[Calendar Quarter]
, 3
, [Date].[Calendar].[Month].[October 2003])
ON 0
FROM [Adventure Works]
returns the parallel period for the month of October 2003 with a lag of three periods, based on the quarter level, which returns the month of January, 2003
3.) PeriodsToDate- returns a set of sibling members from the same level as the given member starting with the first sibling ending with the given member.

Syntax:PeriodsToDate( [ Level_Expression [ ,Member_Expression ] ] )
Arguments:
Level_Expression
A valid Multidimensional Expressions (MDX) expression that returns a level.
Member_Expression
A valid Multidimensional Expressions (MDX) expression that returns a member.

eg following query returns internet sales for the 1st 10 months of 2003
Select [Measures].[Internet Sales Amount] on 0,
PeriodsToDate([Date].[Calendar].[Calendar Year],
[Date].[Calendar].[Month Name].&[10].&[2003]) on 1
from [Adventure Works]

Creating a Calculated Member:
It is a dimension member whose value is calculated at the run time. Imp:The cube members are considered as members of the special Measures dimension.
The definitions of the Cube Calculated members and the named sets become part of the cube MDX Script which can be accessed from the calculation tab.

Calculated Member Syntax:
CREATE MEMBER CURRENTCUBE.[measures].[Sales Amount]
AS [measures].[Internet Sales Amount]+[measures].[Reseller Sales Amount],
FORMAT_String="Currency",
VISIBLE=1;

Calculated Members:
A query can request calculated columns using WITH clause before SELECT
eg

With Member [Gross Profit] AS
'[Measures].[Internet Sales Amount]+[Measures].[Reseller Sales Amount]'
SELECT {[Measures].[Internet Sales Amount],[Gross Profit]} ON COLUMNS,
[Product].[Category].members on Rows
FROM [Adventure Works]
WHERE [Date].[Calender year].&[2003]

When to use Calculated Members:
1.)If a Measure is needed that does not belong to a particular measure group.
2.)when you need a non additive measure that operates uniformly across dimension levels.
eg a calculated member that calculates avg reseller profit amount (Reseller Sales Amount/Reseller Sales Count) returns the expected results no matter how the user slices the data.

Note:
Calculated Member does not increase the cube size since the server stores only its definition, not the data.

Defining Named Sets:
The following MDX construct returns a set of dimension members. eg the below MDX returns top 50 customers:
CREATE SET CURRENTCUBE.[Top 50 Most Profitable Customers]
AS
TopCount(
(Existing [Customer].[Customer].[Customer].Members), 50, [Measures].[Internet Sales Amount]);

Note:
The member function returns all members of the customer attribute hierarchy in the dimension customer table. Use the triple notion syntax eg Dimension.Attribute.Attribute to exclude the All member of the hierarchy.
2.)Provides 2 types of named sets
Static: Evaluated once by the server
Dynamic:Evaluated for each query
By default BIDs creates Dynamic sets.