SSAS + Forecast calculations MDX

Following forecast member returns forecast figures for a given measure based on the entire history.

WITH MEMBER 
[Measures Utility].[Measure Calculations].[Linear Regression Forecast]
AS LinRegPoint(
    Rank(
        [Period].[Extended Year - Quarter - Month].CurrentMember,
        [Period].[Extended Year - Quarter - Month].CurrentMember.Level.Members
        ),
    Exists(
        [Period].[Extended Year - Quarter - Month].CurrentMember.Level.Members,
        [Period].[Calendar Year - Quarter - Month].[Month].Members),

    ([Measures].CurrentMember, [Measures Utility].[Measure Calculations].DefaultMember),
    Rank(
    [Period].[Extended Year - Quarter - Month].CurrentMember,
    [Period].[Extended Year - Quarter - Month].CurrentMember.Level.Members)
)

SELECT
([Measures].[RXA Value] *
[Measures Utility].[Measure Calculations].[Linear Regression Forecast]
) on 0,
NON EMPTY {[Period].[Extended Month].Children} 
ON 1
FROM [Cube Name]

Forecasts based on 3 months data points
CREATE MEMBER CURRENTCUBE.[Measures Utility].[Measure Calculations].[Linear Regression Forecast - 3 Data Points]
AS LinRegPoint(
    Rank([Period].[Operational Year - Quarter - Month].CurrentMember,
            {
            Tail(Exists([Period].[Operational Year - Quarter - Month].Month.Members, [Period].[Calendar Year - Quarter - Month].[Month].Members),1).Item(0).Item(0).Lag(2):
            Tail(Exists([Period].[Operational Year - Quarter - Month].Month.Members, [Period].[Calendar Year - Quarter - Month].[Month].Members),1).Item(0).Item(0).Parent.Parent.LastChild.LastChild
            } --[Output Period]
         ),  -- Measures.OutputX (Current Financial Year)
         Tail(Exists([Period].[Operational Year - Quarter - Month].Month.Members, [Period].[Calendar Year - Quarter - Month].[Month].Members),3), -- [Input Period] -- 3 Months
    (    [Measures].CurrentMember,
        [Measures Utility].[Measure Calculations].DefaultMember), -- Measures.Input Y
    Rank(
        [Period].[Operational Year - Quarter - Month].CurrentMember,
        Tail(Exists([Period].[Operational Year - Quarter - Month].Month.Members, [Period].[Calendar Year - Quarter - Month].[Month].Members),3) --[Input Period] -- 3 Months
        ) -- Measures.InputX
    ),
FORMAT_STRING = "#,#.00",
VISIBLE = 1;

Dynamic Management Views for SSAS

Analysis Services (SSAS) Dynamic Management Views (DMV) are query structures that expose information about local server operations and server health. The query structure is an interface to schema row sets that return metadata and monitoring information about an Analysis Services instance.

SSAS DMVs can be used to monitor the server resources (e.g. connections, memory, CPU, users, aggregation etc. ) and find out the structure of SSAS databases (e.g. hierarchy , dimensions, hierarchies, measures, measure groups, data sources, cubes, actions and KPIs etc.) .

SQL statements can be used to query the row sets, but have following limitation in SQL 2008 version.

• SELECT DISTINCT does not return DISTINCT values
• ORDER BY clause accepts just one field to order by. Only one order expression is allowed for TOP Expression at line 1, column 1″
• COUNT, SUM does not work
• ORDER BY <number> does not ORDER, but no error
• JOINS appear not to work
• LIKE does not work
• String functions like LEFT do not work

Usefull DMVs

SELECT * FROM $system.DBSCHEMA_CATALOGS -- list of the Analysis Services databases on the current connection.

SELECT * FROM $system.DBSCHEMA_COLUMNS

SELECT * FROM $system.DBSCHEMA_PROVIDER_TYPES

SELECT * FROM $system.DBSCHEMA_TABLES

SELECT * FROM $system.DISCOVER_SCHEMA_ROWSETS

SELECT * FROM $system.DISCOVER_COMMANDS

SELECT * FROM $system.DISCOVER_CONNECTIONS

SELECT * FROM $system.DISCOVER_JOBS

SELECT * FROM $system.DISCOVER_LOCKS

SELECT * FROM $system.DISCOVER_MEMORYUSAGE ORDER BY MemoryUsed DESC

SELECT * FROM $system.DISCOVER_OBJECT_ACTIVITY

SELECT * FROM $system.DISCOVER_OBJECT_MEMORY_USAGE 

    ORDER BY OBJECT_MEMORY_NONSHRINKABLE DESC

SELECT * FROM $system.DISCOVER_SESSIONS

SELECT * FROM $system.MDSCHEMA_CUBES

SELECT * FROM $system.MDSCHEMA_DIMENSIONS

SELECT * FROM $system.MDSCHEMA_FUNCTIONS

SELECT * FROM $system.MDSCHEMA_HIERARCHIES

SELECT * FROM $system.MDSCHEMA_INPUT_DATASOURCES

SELECT * FROM $system.MDSCHEMA_KPIS

SELECT * FROM $system.MDSCHEMA_LEVELS

SELECT * FROM $system.MDSCHEMA_MEASUREGROUP_DIMENSIONS 

    -- WHERE MEASUREGROUP_NAME = 'NSA NZ Sales'

SELECT * FROM $system.MDSCHEMA_MEASUREGROUPS

SELECT * FROM $system.MDSCHEMA_MEASURES

SELECT * FROM $system.MDSCHEMA_MEMBERS

SELECT * FROM $system.MDSCHEMA_PROPERTIES

SELECT * FROM $system.MDSCHEMA_SETS

Using DMV Queries to get Cube Metadata.

Referenceshttps://msdn.microsoft.com/en-us/library/hh230820(v=sql.110).aspxhttps://dwbi1.wordpress.com/2010/01/01/ssas-dmv-dynamic-management-view/
http://blogs.microsoft.co.il/yanivmor/2010/01/27/dmvs-for-analysis-services/
https://bennyaustin.wordpress.com/2011/03/01/ssas-dmv-queries-cube-metadata/

MDX - Parsing a value from one dim to another

Following query pass year 2016 value from [Period] dimension to [Exchange Rate] Dim.

WITH 
member YearX as 
NONEMPTY(
[Period].[Operational Year - Semester - Quarter - Month].[Year].&[2016] ,
[Measures].[Targets - EdoxabanNetSalesTargets]).item(0).Properties("Key")

SELECT  {[Measures].[Exchange Rate]}
on 0, non empty
 (
 [Exchange Rate].[From Currency].[From Currency]
, [Exchange Rate].[To Currency].[To Currency]
)
on 1 from [XXXX Cube]
where STRTOMEMBER( "[Exchange Rate].[Operational Year Key].[Operational Year Key].&[" + 
YearX
+ "]")

Working with System date + SSAS + MDX

The purpose of following MDX query is to create the CompleteDataMonthFlag flag which can be used to identify the latest month with complete data. For example, if we receive data with granularity less than a month (i.e. weekly or daily), we could use this flag to identify the latest month with complete data.

WITH 
// Following set is to get the system year and month
SET [System Month - Operational] as 
    StrToMember("[Period].[Operational Year - Quarter - Month].[Month].&[" + Format(now(), 'yyyy') + Format(now(), 'MM')+"]")
// Following set is to get the month with latest data
SET [Latest Data Month - Operational] AS Tail(Nonempty([Period].[Operational Year - Quarter - Month].[Month].members), 1)
//Member [Measures].[LatestDataMonth] AS LatestDataMonth.item(0).name
//Member [Measures].[CurrentMonth] AS CurrentMonth.item(0).name
SET LatestMonth AS
    CASE
    WHEN [Latest Data Month - Operational].item(0)  = [System Month - Operational].item(0)
        THEN [Latest Data Month - Operational].item(0).lag(1)  
    WHEN [Latest Data Month - Operational].item(0)  <> [System Month - Operational].item(0)
        THEN [Latest Data Month - Operational].item(0) 
    END      
MEMBER [Measures].[CompleteDataMonthFlag] as
    IIF ([Period].[Operational Year - Quarter - Month] is LatestMonth.Item(0),1, 0)

SELECT {
[Measures].[CompleteDataMonthFlag]
}
ON 0 ,
NON EMPTY
[Period].[Operational Year - Quarter - Month].[Month].members
ON 1
FROM [Cube Name];

IF the [CompleteDataMonthFlag] needs to be implemented within the SSAS cube as a calculated member, the following script can be used

Create Set CurrentCube.[System Month - Operational] AS 
    StrToMember("[Period].[Operational Year - Quarter - Month].[Month].&[" + Format(now(), 'yyyy') + Format(now(), 'MM')+"]");
Create Set CurrentCube.[Latest Data Month - Operational] AS 
    Tail(Nonempty([Period].[Operational Year - Quarter - Month].[Month].members), 1);
Create Set CurrentCube.LatestMonth AS
    CASE
    WHEN [Latest Data Month - Operational].item(0)  = [System Month - Operational].item(0)
        THEN [Latest Data Month - Operational].item(0).lag(1)
    WHEN [Latest Data Month - Operational].item(0)  <> [System Month - Operational].item(0)
        THEN [Latest Data Month - Operational].item(0)
    END;     
CREATE MEMBER CURRENTCUBE.[Measures].[CompleteDataMonthFlag] as
    IIF ([Period].[Operational Year - Quarter - Month] is LatestMonth.Item(0),1, 0);

Ref: 
http://sqlblog.com/blogs/mosha/archive/2007/05/23/how-to-get-the-today-s-date-in-mdx.aspx
http://sqljoe.wordpress.com/2011/07/22/dynamically-generate-current-year-month-or-date-member-with-mdx/

SSAS Scope function examples

Define scope to a base measure.
SCOPE ([Measures].[Meetings]);
    this = ([Measures].[Meetings], [Contact Detail].[Event Type].&[Meeting]);
END SCOPE;

Creating a calculated member which use mutilple measures for different hierarchies.
Following calculated member can be used to define measures for different hierarchies. When [Target] measure is selected against  [Territory].[Territory] hierarchy,  it returns [Measures].[Territory Target].Otherwise it returns [Measures].[Area Target].

CREATE MEMBER CURRENTCUBE.[Measures].[Target]
    AS [Measures].[Area Target],
FORMAT_STRING = "#,#0",
NON_EMPTY_BEHAVIOR = [Measures].[Area Target],
VISIBLE = 1;
SCOPE ([Measures].[Target], [Territory].[Territory].[Territory].members);
    this = [Measures].[Territory Target];
    NON_EMPTY_BEHAVIOR(this) = [Measures].[Territory Target];
END SCOPE;

Create sub scope statementIn following calculated member, measure is limited to Efient products. (i.e defined in the sub scope statement). it returns null for other products.
CREATE MEMBER CURRENTCUBE.[Measures].[Stg - Retail Targets]
    AS NULL,
FORMAT_STRING = "#,#0",
VISIBLE = 1;
SCOPE ([Measures].[Stg - Retail Targets]);
    SCOPE( [Product].[Product].[EFIENT]);
    this = ([Measures].[Area Target] - [Measures].[Stg - SCM Hospital Targets]);
    END SCOPE;
END SCOPE; 

SSAS [Period Utility] calculations + Accumulated (Addictive) figures

The following calculated members return accumulated figures for a given measure and selected level in period dimension (you need to have a period (time) utility dimension in the cube in order to create this member).
YTD (Year To Date Calculation)
CREATE MEMBER CURRENTCUBE.[Period Utility].[Period Accumulations].[YTD (Operational)]
AS NULL,
FORMAT_STRING = "#,#.0",
VISIBLE = 1;    
SCOPE ([Period Utility].[Period Accumulations].[YTD (Operational)]);  
    this = SUM(
               PeriodsToDate(
                   [Period].[Operational Year - Quarter - Month].[Year],
                   [Period].[Operational Year - Quarter - Month].CurrentMember                   
               ), ([Measures].CurrentMember, [Period Utility].[Period Accumulations].DefaultMember)
           );  
END SCOPE;   Following MDX can be used to validate figures.
SELECT
(
{[Measures].[Stg - Measure Name]} * {[Period Utility].[Period Accumulations].[YTD (Operational)]}
) 
On Columns,
Non Empty {
[Period].[Operational Year - Quarter - Month].[Month].members
} On Rows
From [Cube Name]
 









QTD (Quater To Date Calculation)
CREATE MEMBER CURRENTCUBE.[Period Utility].[Period Accumulations].[QTD (Operational)]
AS NULL,
FORMAT_STRING = "#,#.0",
VISIBLE = 1;    
SCOPE ([Period Utility].[Period Accumulations].[QTD (Operational)]);  
    this = SUM(
               PeriodsToDate(
                   [Period].[Operational Year - Quarter - Month].[Quarter],
                   [Period].[Operational Year - Quarter - Month].CurrentMember                   
               ), ([Measures].CurrentMember, [Period Utility].[Period Accumulations].DefaultMember)
           );  
END SCOPE;   

Following MDX can be used to validate figures.
SELECT
(
{[Measures].[Stg - Measure Name]} * {[Period Utility].[Period Accumulations].[QTD (Operational)]}
) 
On Columns,
Non Empty {
[Period].[Operational Year - Quarter - Month].[Month].members
} On Rows
From [Cube Name]