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; 

Useful SSIS Expressions

Search Expressions

• If you want to find a specific text (e.g. 'Value') in a word ( underValue1), you could use following expression:
      FINDSTRING(col, "value", 1) > 0
• If you want to find a specific text (e.g. 'Value') at the beginning of a word (Value1), you could use following expression:
     FINDSTRING(col, "value", 1) == 1
• If you want to find a specific text (e.g. 'value') at the end of a word (underValue), you could use following expression:
     REVERSE(LEFT(REVERSE(col), X)) == "Value"

Assign NULL values (tested in version 2012 )

    (DT_STR, 4, 1252)NULL(DT_STR, 4, 1252)

Character replace expressionsUse hex notations to remove special characters in a fields of a Data Conversion transformation.

• TRIM(REPLACE(ColumnName,"\x0009","")) - Remove horizontal
• TRIM(REPLACE(ColumnName,"\x000D","")) - Remove carriage
• TRIM(REPLACE(ColumnName,"\x000A","")) - Remove line feeds
• TRIM(REPLACE(ColumnName,"\x00A0","")) - Remove Non breaking space (HTML tag - &nbsp)
  
  Hex codes added in SSIS expression should have 6 characters that should start with "\x" and end with 4 characters. Therefore we might need to add extra 0s. (e.g to replace "&" we should use REPLACE(ColumnName,"\x0026","") )
  
  Following websites can be used to find the hex code for known characters.
  http://www.ascii-code.com/
  
  

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]

SSIS 2005 - Read data from an excel 2007 file and format it

In order to read data from an Excel 2007 file, create an OLE DB connection and add file path and the file name as the server name.


Read data from [CountryOverview] sheet.
If you select "Table name or View name" as the data access mode, you could select the sheet name which you wish to read from the drop down menu. Instead of that, as you can see in the image below, a SQL query can be writted to read data from excel sheet.

SQL Server Agent Jobs to Backup / Restore SSAS Cube

This post demostrates a few scripts which can be used within SQL Server Agent Jobs.

XMLA Script to backup a cube
<Backup xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
  <Object>
    <DatabaseID>Company UAT Cube</DatabaseID>
  </Object>
  <File>Company UAT Cube.abf</File>
  <AllowOverwrite>true</AllowOverwrite>
  <Password>AABB01</Password>
</Backup>

XMLA Script to restore a .ABF file

<Restore xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
  <File>C:\Data\SQL Server Data\MSSQL.2\OLAP\Backup\Company Test Cube.abf</File>
  <DatabaseName>Company Test Cube</DatabaseName>
  <AllowOverwrite>true</AllowOverwrite>
  <Password>AABB01</Password>
</Restore>
SSAJ - operating system command to copy a file
copy "D:\FTP Root\Test Cube.abf" "D:\Microsoft SQL Server 2005\Analysis Services\MSSQL.2\OLAP\Backup\Test Cube.abf" /Y

SSAJ - operating system command to run a batch file
cmd.exe /c "C:\PharmaAnalytics\Daiichi Sankyo Europe Instance\System\Scripts\CopyTestCubeToSrerver1ABFBackups.bat"

Batch script to FTP an ABF file
@echo off
echo user USERNAME PASSWORD>ftp.ftp
echo put "C:\Data\SQL Server Data\MSSQL.2\OLAP\Backup\Cube Test.abf">>ftp.ftp
echo bye>>ftp.ftp
ftp -n -s:ftp.ftp 94.1.1.2
del /q ftp.ftp

SSAS Local Cube

1         Creating a local cube

Local cubes and local mining models allow analysis on a client workstation while it is disconnected from the network. For example, a client application might call the OLE DB for OLAP 9.0 Provider (MSOLAP.3), which loads the local cube engine to create and query local cubes, as shown in the following illustration:

Ref: Microsft

1.1        Create the XMLA script

Create the XMLA script using server cube. Then copy script to Clipboard.

1.2        Create the local cube file and connect to the local cube.

Specify the local cube path and the filename (including .cub as the extention) as the server name. Then press "connect" which will create cube file.

1.3        Open a new XMLA query

Open a new XMLA query window by right clicking on local cube connection and paste the script copied above.

1.4        Execute the script file

You can search for following elements in the script file to make sure that you are connecting to right database.

<ConnectionString>

1.5        Process the cube

Right click on the local cube database and click “Process”

Ignore following error messages

Value cannot be null.

Parameter name: key (System)

2         Access local cube file

2.1        Microsoft Excel

.cub file can be simply browsed using Excel (2010/2013) and you will get the same interface that you get when access SSAS server cube using Excel.

2.2        XLCubed

.cub file can be also simple connected to XLCubed by creating XLCubed connection. Connection type should be “Analysis Service Cube File.

3         Benefits

•  Local cube files can dramatically improve browsing speed performance, especially when analysing low levels of large dimensions.
• Local cube files can also improve browsing performance because requests for additional data are handled on the local computer rather than across a network on an Analysis Server.
•  Local cube files can now be encrypted and password-protected.
• Analysis Services 2005 provides more precise control over the creation of local cube files.
• When you use local cube files you can give each user the specific data they need, or want, or are allowed to see.

4         Limitations

• When creating local cubes from server-based cubes, the following considerations apply:
•  Distinct count measures are not supported.
• When you add a measure, you must also include at least one dimension that is related to the measure being added.
• When you add a parent-child hierarchy, levels and filters on a parent-child hierarchy are ignored and the entire parent-child hierarchy is included.
• Member properties are not created.
• When you include a semi-additive measure, no slices are permitted on either the Account or the Time dimension.
• Reference dimensions are always materialized.
• When you include a many-to-many dimension, the following rules apply: 
• You cannot slice the many-to-many dimension.
•  You must add a measure from the intermediary measure group.
• You cannot slice any of the dimensions common to the two measure groups involved in the many-to-may relationship.
• Only those calculated members, named sets, and assignments that rely upon measures and dimensions added to the local cube will appear in the local cube. Invalid calculated members, named sets, and assignments will be automatically excluded.

Ref: http://technet.microsoft.com/en-us/library/bb522640.aspx