MDX Training Notes
Day 1
1. Performance Point
2. Dimensions that don’t have enough levels will slow down the performance. If the source doesn’t have levels, we need to create some in the dimensions
3. Aggregation helps on performance when you have big fact tables and small number of dimensions.
4. Performance
a. The actual run time for MDX
b. How fact the Cube is being processed
5. Caching is important for SSAS performance. How to create cache?
6. It is possible to create an aggregation just for one MDX query in order to improve performance.
7. ProClarity is better than Excel in terms of MDX and SSAS.
8. Calculated member can be created in three places: Cube, session, query
9. Default measure can be found in the advanced cube properties section in cube design window.
10. In SQL Server mgmt studio query window, you can select a section and just run the highlighted query
11. Models in Performance Point are Cubes in SSAS
12. Empty rows or columns mean the whole row or column has null value.
13. Density of Cell set. (Use Non Empty on rows and columns)
14. Muiltiple keys for a member
a. [Date].[Calendar].[Calendar Semester].&[2002]&[1] is the same as [Date].[Calendar].&[2002].&[2002]&[1] or [Date].[Calendar].&[2002].&[1]
15. When use tuples, the dimensions that are not listed use the default member, most of the time it’s the all member.
16. Meauses can be treated as demensions in most cases. E.g.
SELECT (
[Measures].[Internet Sales Amount], [Department].[Departments].[Department Level 01]
) ON 0
FROM [Adventure Works]
WHERE [Date].[Day of Year].[Day of Year].members
17. A set requires the same dimensionality on all its members.
18. To get rid of the all_member in a SET, specify the level in [Demension].[Hieracky].[Level]
19. .allmembers IS different than .members. .allmembers returns calculated members while .members doesn’t.
20. How to specify data type for calculated members?
21. CurrentMember function expects hierarchy
22. In SQL mgmt studio query window, put go between queries will run all the queries at the same time.
23. In Lab 05A for the Calculated member, on the Calendar Year hierarchy, [Date].[Calendar].[All Periods].[2004 Bikes] is wrong because [All Periods] is a hierarchy or level not a member(parent), instead [Date].[Calendar].[All Periods].[2004 Bikes] is correct because [All Periods] is a member.
24. Most VBA functions work in MDX
25. MDX is smart enough to handle data types by itself on calculated members
a. Dd
b. Bb
26. CrossJoin’s order does matter. The first one shows up first.
27. Attributes Hierarchy has two levels, all and the list of all members. Meanwhile, User Hierarchy is the user defined multi-level hierarchy.
28. When use Descendants() function without specifying level, it includes itself.
Day 2
1. .Currentmember is almost always optional. But for readability, use .currentmember as a good practice.
2. List of properties of a dimension can be found in BIS dimension structure window.
3. To see the cube definitions of a SSAS database without the solution file, on BIS select File ànew àprojectàImport SSAS database. A new project with the definitions will be created. Then right click on the solution and go to properties, change the server and database name, and do a deployment. That will create a copy of the database to your local server for you to play with.
4. you will get a null value. Instead you should use something like member.name to return a string or value.
5. To format strings in MDX: [cell value], format_String=”Percent”
6. User ORDER function to sort MDX results
7. NonEmpty() is a function while Non Empty is a Keyword
8. NonEmpty() is MUCH faster than filter. E.g. The first below one is much faster than the second one.
WITH
MEMBER Measures.CustomerCount AS
COUNT(NONEMPTY([Customer].[Customer Geography].[Customer].MEMBERS, [Measures].[Internet Sales Amount]))
WITH
MEMBER Measures.CustomerCount AS
COUNT(Filter([Customer].[Customer Geography].[Customer].MEMBERS, [Measures].[Internet Sales Amount]>0))
9. Generate() function ?????
10. Order() function. Use BDESC instead of DESC. BDESC sorts all members regardless of the hierarchy, while DESC sorts only within the hierarchy. B means breaking.
11. Rank() can be used as a row number column.
with member
measures.test as rank([Product].[Product Categories].currentmember,
[Product].[Product Categories].[Product].members)
select
{[Measures].[Internet Sales Amount], measures.test} on 0,
[Product].[Product Categories].[Product].members on 1
from [Adventure works]
12. .CurrentMember is NOT always the default.
SELECT [Measures].[Internet Sales Amount] ON 0,
ORDER(
[Product].[Product Categories].MEMBERS
, [Product].[Product Categories].CurrentMember.NAME, BDESC)
ON 1
FROM
[Adventure Works]
Is correct, While
SELECT [Measures].[Internet Sales Amount] ON 0,
ORDER(
[Product].[Product Categories].MEMBERS
, [Product].[Product Categories].NAME, BDESC)
ON 1
FROM [Adventure Works]
Sorts members by hierarchy instead of members.
13. SetToStr etc is usually used for debugging.
14. Exists() function is faster than Filter() function. Exists() works with two hierarchies in the same dimension.
15.
