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/

Backup and Restore Multiple Databases and Cubes

SSAS Cubes
1. List all the cube names in a MS Analysis Server instance.
Open a MDX / DAX query window and run followoing DMVquery.
    SELECT * FROM $system.dbschema_catalogs
    ORDER BY [catalog_name] ASC

2. Backup multiple databases in a MS Analysis Server instance.
Sometimes, database name may not be same as database ID. In that case, DatabaseID needs to be found by going to properties of the database.
<Batch xmlns="http://schemas.microsoft.com/analysisservices/2003/engine" Transaction="false" >
   <Backup xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
       <Object>
       <DatabaseID>Cube 2</DatabaseID>
       </Object>
       <File>C:\Data\SQL Server Data\MSSQL.2\OLAP\Backup\Cube 1.abf</File>
       <AllowOverwrite>true</AllowOverwrite>
   </Backup>
   <Backup xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
       <Object>
       <DatabaseID>C:\Data\SQL Server Data\MSSQL.2\OLAP\Backup\Cube 2</DatabaseID>
       </Object>
       <File>Cube 2.abf</File>
       <AllowOverwrite>true</AllowOverwrite>
   </Backup>
</Batch>
Ref: http://blog.sqltechie.com/2010/01/how-to-backup-multiple-database-using.html

SQLServer Databases
1. Backup all the databases in a SQLServer  instance can be done using following script (you can exclude any database by defining it within the where clause of select statement).

DECLARE @name VARCHAR(50) -- Database name 
DECLARE @path VARCHAR(256) -- Backup files' path
DECLARE @fileName VARCHAR(256) -- Backup files' filename
DECLARE @fileDate VARCHAR(20) -- used for file name

-- specify database backup directory
SET @path = 'D:\Microsoft SQL Server 2005\Analysis Services\MSSQL.2\OLAP\Backup\DatabaseMigration-09-06-2014\' 

-- specify filename format
SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112)

DECLARE cursor1 CURSOR FOR 
SELECT name FROM master.dbo.sysdatabases
WHERE name NOT IN ('master','model','msdb','tempdb','Report Administration','ReportServer','ReportServerTempDB' )

OPEN cursor1  
FETCH NEXT FROM cursor1 INTO @name  

WHILE @@FETCH_STATUS = 0  
BEGIN  
       SET @fileName = @path + @name + '.BAK' 
       BACKUP DATABASE @name TO DISK = @fileName 

       FETCH NEXT FROM cursor1 INTO @name  
END  
CLOSE cursor1  
DEALLOCATE cursor1

2. Restore database using following command
Back files need to be copied to MS SQL Server\MSSQL.1\MSSQL\data Folder.
RESTORE DATABASE [AnalyticsPortal]
   FROM DISK = 'D:\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\AnalyticsPortal.BAK'
   WITH MOVE 'AnalyticsPortal' TO 'd:\Microsoft SQL Server\MSSQL.1\MSSQL\data\AnalyticsPortal.mdf',
   MOVE 'AnalyticsPortal_log' TO 'd:\Microsoft SQL Server\MSSQL.1\MSSQL\Data\AnalyticsPortal_1.LDF'

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;