Misc
Cells are the intersections of dimensions - they can have multiple measures within them.
Tuples are the sections of statements that identify slices e.g. Dimension.level.member
Parentheses surround tuples that contain more than a single dimension in MDX notation.
A named set is an ordered collection of tuples given an alias.
The parent dimension of a calculated member will determine where it is displayed -
if you set something to be a member of a dimension then it will appear on that dimensions hiearchy (at the top by default).
If you further specify a parent member such as 1998 then the calc member will only appear under that member.
"Common examples of set usage include axis dimensions (the dimensions and members to be returned, specified in the SELECT statement in the query, as we will see) and slicer dimensions (the specific dimension and member criteria to which the returned data is restricted, used by the WHERE statement, as we will discover)"
Diff between CHILDREN and MEMBERS: MEMBERS also gives all level if present.
Cell Properties
This can have in incredible effect on performance when just the values are requested and not the formatted value. 25 second query down to 1 second as profiler shows 'serializing' which is really applying cell properties. Add CELL PROPERTIES VALUE to the end of a query to get (very performant) value only:
SELECT X ON 1, Y ON 2
FROM cubename
CELL PROPERTIES VALUE
Named sets
WITH SET FAVYEARS
AS {Date.Fiscal Year.&2002, Date.Fiscal Year.&2005}
SELECT Date.Calendar Year.MEMBERS ON COLUMNS,
FAVYEARS ON ROWS
FROM Adventure Works
WHERE Measures.Internet Sales AmountOr have a session scoped set:
CREATE SESSION SET Adventure Works.FAVYEARS
AS {Date.Fiscal Year.&2002, Date.Fiscal Year.&2005}
SELECT Date.Calendar Year.MEMBERS ON COLUMNS,
FAVYEARS ON ROWS
FROM Adventure Works
WHERE Measures.Internet Sales Amount
Calculated Member
Is actually a calculated measure.
WITH MEMBER MEASURES.DOUBLESALES
AS Measures.Internet Sales Amount * 2
SELECT Date.Calendar Year.MEMBERS ON COLUMNS,
Date.Fiscal Year.Fiscal Year.MEMBERS ON ROWS
FROM Adventure Works
WHERE MEASURES.DOUBLESALES
Or a session scoped calculated member
CREATE SESSION MEMBER
Adventure Works.MEASURES.SESSIONDOUBLESALES
AS
Measures.
Internet Sales Amount * 2
SELECT
Date.
Calendar Year.MEMBERS ON COLUMNS,
Date.
Fiscal Year.
Fiscal Year.MEMBERS ON ROWS
FROM
Adventure Works
WHERE MEASURES.SESSIONDOUBLESALES
E.g.
CREATE MEMBER CURRENTCUBE.Measures.ProfitRatio AS 'Measures.Store Sales/Measures.Store Cost'
VBA
and Excel libraries are registered within AS runtime so all functions are accessible BUT only a few of them are 'internal' - in the AS app domain, making them as fast as native MDX functions. List on tint.
VBA!Left(blah) - is an example of VBA in use within MDX. Simple as that.
e.g.
WITH MEMBER CORRUPTYEAR
AS vba!Left(Measures.Internet Sales Amount,10)
SELECT Date.Calendar Year.MEMBERS ON COLUMNS,
Date.Fiscal Year.CHILDREN ON ROWS
FROM Adventure Works
WHERE CORRUPTYEAR
SUBCUBE
Bit like a view.
The following example creates a subcube that restricts the Budget cube to only accounts 4200 and 4300:
Having created a subcube for the session, any subsequent queries will be against the subcube, not the whole cube. For example, you run the following query. This query will only return members from accounts 4200 and 4300.
CREATE SUBCUBE Budget AS SELECT {Account.Account.&4200, Account.Account.&4300 } ON 0 FROM Budget
SELECT Account.Account.Members ON 0, Measures.Members ON 1 FROM Budget
FILTERING/RESTRICTING
FILTER
SELECT
{Measures.Warehouse Sales} ON COLUMNS,
{Filter (
Warehouse.City.MEMBERS,
(Measures.Warehouse Sales, Time.1998) > 9999)
} ON ROWS
FROM
WarehouseOr when you need to restrict on member value, not measure value use one of the 3 keywords:
MemberValue
Member_Key
Member_Name
as in:
SELECT
{ FILTER
(Order
(Ascendants(Management.Cartesis Hierarchy.&10)
,DESC )
,Management.Cartesis Hierarchy.member_key <> "1")
} ON COLUMNSHAVING: Use having to restrict on measure values, post NON-EMPTY. 2005 onwards. Nothing that couldnt be done before 2005.
.Level keyword
Once applied to a member, the Ordinal, Name and other properties are accessible.
FILTER(Ascendants(Management.Cartesis Hierarchy.&10)
,Management.Cartesis Hierarchy.Level.Ordinal > 3)
DRILLTHROUGH statement
Funny syntax to get normal 2d recordset. Note that a single cell must be returned, hence only the mdx select on 0 - a single axis, as opposed to cols and rows, which would make a matrix and therefore it wouldnt work. This is VERY important. Remember the single cell is what would be returned to a client, it does NOT mean an intersection at the lowest level of granualarity.
The single cell can return high levels in hierarchies, meaning many rows will be returned.
Notice that .members or .children will never be used, its all about all axis defaulting to their .all member (which is a sum total as opposed to getting all members and therefore going over 1 cell), unless an axis member is given, such as November in below example.
DRILLTHROUGH
SELECT
(Date.Year-Month.Month.&2008&November)
ON 0
FROM HR DW
WHERE CartesisReporting.ReportingHierarchy.&1
RETURN
$Date.Year
,key($Date.Year)
,name($Date.Year)
,uniquename($Date.Year)
,caption($Date.Year)
,membervalue($Date.Year)
,CustomRollup($Date.Year) //blank by default
,CustomRollupProperties($Date.Year) //blank by default
,unaryoperator($Date.Year) //blank by default
,Fact Compensation.Amount
DIMENSION PROPERTIES
"extended field information for Analysis Services" is the section of a query that requests and additional properties to be returned for each dimension member.
SELECT { Measures.HeadCount } ON COLUMNS, { (Management.Cartesis Hierarchy.CHILDREN ) }
DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME, PARENT_UNIQUE_NAME, LEVEL_NUMBER ON ROWS
These properties are automagically picked up by reporting services and accessible through code/dropdowns.
DESCENDANTS
Will retrieve child members, default is all nodes inc leaf, or a specified number of levels or with the LEAVES keyword all leaf level members.
{DESCENDANTS(Region.Country Region.&110) } //ALL descendants, leaf and mid level nodes
{DESCENDANTS(Region.Country Region.&110,2) } //2 levels deep
{DESCENDANTS(Region.Country Region.&110,,LEAVES) } //all leaf level
COALESCEEMPTY
Replaces empty cells with whatever value you supply
WITH Member Measures.InternetSalesX as
CoalesceEmpty(Measures.Internet Sales Amount,"x")
EXISTS
forces a specified set to be evaluated within the current context instead of being evaluated within the context of the cube that contains the members of the set. Basically counts may give you cube level totals but you only want totals for the set you are working on.
WITH MEMBER CITY_COUNT AS COUNT(GEOGRAPHY.CITY.CITY.MEMBERS)
SELECT {CITY_COUNT} ON 0
,GEOGRAPHY.GEOGRAPHY.COUNTRY.MEMBERS ON 1
FROM ADVENTURE WORKS
will give 587 for all members - wrong!
WITH MEMBER CITY_COUNT AS COUNT(EXISTING(GEOGRAPHY.CITY.CITY.MEMBERS))
SELECT {CITY_COUNT} ON 0
,GEOGRAPHY.GEOGRAPHY.COUNTRY.MEMBERS ON 1
FROM ADVENTURE WORKS
will give correct counts for all members.
NON EMPTY
NON EMPTY keyword is the classic - it works at the top level and will remove result axis members that have NULL for ALL measures. This is resource intensive operation that operates across all dimensions - scattergun approach which is fine if you simply want to get rid of members\cells all together.
NONEMPTY()
Takes 2 params: dimension and measure. Allows finer tuning than NON EMPTY as if the given measure has a value somewhere along the given dimension it will retain the cell as a NULL. While the NON EMPTY keyword can only be applied to an axis, the NonEmpty function can be applied to a set.
Ordering expression arguments
When writing calculation expressions like “expr1 * expr2”, make sure the expression sweeping the largest area/volume in the cube space and having the most Empty (Null) values is on the left side. For instance, write “Sales * ExchangeRate” instead of “ExchangeRate * Sales”, and “Sales * 1.15” instead of “1.15 * Sales”. This is because the Query Execution Engine iterates the first expression over the second expression. The smaller the area in the second expression, the fewer iterations the Query Execution Engine needs to perform, and the faster the performance.
HIERARCHIZE
The Hierarchize function organizes the members of specified set into hierarchical order. If you wrap dat dimension years, quarters and months up into a set and then wrap that in HIERARCHIZE, it will sort the members into the correct order: