Using Annotation Tables in SSAS to Show Last Processed Time and Latest Data Updates as a Measure in a Cube

Annotation Tables and leveraging the fact the formula engine of SSAS support non numeric calculation results is a very strong feature that can be leveraged to show just about anything as a measure in a cube. 

In this article I am going to walk through how you can expose two pieces of useful information to end user as simple measures in a cube.   The two are Cube Last Processed Date and Latest Data Update Date.

Creating a Named Query

We start by  creating a named query in the data source view that we can base a System Annotations dimension on.  In my example the query returns three columns but only two of them are required Data_Key and Data.

The named query looks like this:

 

The query looks like this:

SELECT    

      1 AS Data_Key

      ,CONVERT(varchar(1024), GETDATE(), 121) AS Data

      , 'Cube Process Date' AS Data_Desc

UNION ALL

SELECT

      2 AS Data_Key

      , CONVERT(varchar(1024),

        (SELECT MAX(LastUpdateDate) AS Expr1 FROM         

           (SELECT MAX(LastUpdateDate) AS LastUpdateDate FROM Table1

                UNION ALL

            SELECT MAX(LastUpdateDate) AS Expr1 FROM Table2

           ) AS X

         ), 121) AS Data

    , 'Latest Data Update Date' AS Data_Desc

The dataset returned has 3 columns and 2 rows one for each of the new measures that will be added.

Data_Key	Data	Data_Desc
1	2010-10-29 10:42:32.593	Cube Process Date
2	2010-10-29 10:42:32.593	Latest Data Update Date

Before we proceed to the next step and create a dimension based on the named query just created.  We need to make sure we set the primary key of the named query to Data_Key.

Creating a System Annotation Dimension

We can use the wizard to quickly create a System Annotation dimension based on the table above.  It should look something like this:

 

Once the dimension is created we need to add it to the cube using the dimension Usage tab in the cube designer.  The dimension does not slice any of your existing measure groups so we just add it an that is it.  We can also on the cube structure tab we hide the dimension so that it does not show up when browsing the cube.

The last thing we need to do is create the measures.

Creating Measures Based on the System Annotation Dimension

To expose the Cube Last Processed Date and the Data Last Updated Date measure we need to create two calculation in the cube.  The script for these calculation looks like:

CREATE MEMBER CURRENTCUBE.[Measures].[Cube Last Processed On]

 AS [System Annotations].[Data].&[1].MemberValue,

FORMAT_STRING = "Standard",

VISIBLE = 1 ,  DISPLAY_FOLDER = 'System Annotations';

 

CREATE MEMBER CURRENTCUBE.[Measures].[Latest Data Updates]

 AS [System Annotations].[Data].&[2].MemberValue,

FORMAT_STRING = "Standard",

VISIBLE = 1 ,  DISPLAY_FOLDER = 'System Annotations';

All these measures are doing is pulling the MemberValue for the a dimension attribute related to the measure.  They are also putting the measures in a folder called System Annotations so that they will show up in the the browsing toll like Excel in a folder. 

Conclusion

Although the Cube Last Processed on Date is not actually the cube processed date kept by SSAS it is close enough to give the user the feedback they need to know when the cube was processed.  You do need to reprocess the System Annotation dimension each time your process the cube.  Also notice that I left the data value in as a string so that it can contain anything you need it without having to worry about the formatting.  The use of Annotation tables in SSAS can solve many problems for example if you had additional data that you wanted to expose from other data sources you could do that by only storing the key value in the fact as a measure hide the measure and use the key to access the dimension attributes.  There are other ways to do the same thing but this example shows how annotation tables can be used to create measures that contain pretty much anything you want.