Show last cube data update date and time

During a workshop session a user requested to know exactly at what time the last data refresh occurred. I think that it is a very fair request and I started investigating to find what could be a good way to implement this.

After finding that we can’t get this information from the cube metadata directly in Excel, I found a web site describing another way to get track of the refresh date and later I just needed to figure out how to show that information in Excel services in an elegant manner.

The first step to do is to record the last update date of the system. To do this we will create a new Named Query in the Data Source View that we are using for our cubes.

The query that I’m using for the Named Query is:

SELECT     1 AS ID, CONVERT(nvarchar(255), GETDATE(), 106) AS Data, ‘Last Update Date’ AS DataDesc

UNION ALL

SELECT     2 AS ID, CONVERT(nvarchar(255), GETDATE(), 108) AS Data, ‘Last Update Time’ AS DataDesc

UNION ALL

SELECT     3 AS ID, CONVERT(nvarchar(255), GETDATE(), 106) + ‘ ‘ + CONVERT(nvarchar(255), GETDATE(), 108) AS Data, ‘Last Update’ AS DataDesc

As you can see this query will create a table with 3 rows and 3 columns. The first row contains just the Date, the second the Time and the third and last row the combination of Date and Time.

After you have created the Named Query and closed the query editor window, don’t forget to right click on the ID column and set it as the Key column. I called the Named Query with the name: SystemData.

The next step is to create a Dimension out of the Named query. I decided to create a cube just for that Dimension. The reason behind is that I don’t want to alter any of my already running cubes and that the data source view is shared by more than one cube. By creating an independent cube we ensure that the same data will be shared by all and that we will not have the need to alter any existing cube.

The new cube, called in my case BI – System Data will have the named query as both Measure Group and Dimension.

In the System Data dimension add the “Data” column to the Attributes list and set the following properties:

KeyColumns = SystemData.ID

NameColumn = SystemData.DataDesc

ValueColumn = SystemData.Data

At this point you should process the System Data dimension. It is important to know that the Named Query data will be refreshed only when the dimension is processed. If the cube is processed but the dimension is not processed, the data will not be updated.

After processing the dimension, the next step is to process the cube itself. These two steps need to be done before we can add a Calculation to the cube.

In the Calculations section we will add 3 calculated members that will expose the data in the System Data named query table as measures by adding the following code:

CREATE MEMBER CURRENTCUBE.[Measures].[Last Update Date]  

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

FORMAT_STRING = “Standard”, VISIBLE = 1 ,  DISPLAY_FOLDER = ‘System Data’  ;

CREATE MEMBER CURRENTCUBE.[Measures].[Last Update Time]  

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

FORMAT_STRING = “Standard”, VISIBLE = 1 ,  DISPLAY_FOLDER = ‘System Data’  ;

CREATE MEMBER CURRENTCUBE.[Measures].[Last Update]  

AS [System Data].[Data].&[3].MemberValue,

FORMAT_STRING = “Standard”, VISIBLE = 1 ,  DISPLAY_FOLDER = ‘System Data’  ;

Now we are ready to process and deploy the cube by the last time. Next step will be to show this information in an Excel spreadsheet and publish it to Excel services with the correct refresh settings.

As you will see, once you open the cube in the Excel file you just can choose any of the 3 data fields that were created in the Calculations tab.

In order to always display the correct data I’ve found that a good practice is to configure the connection inside Excel to refresh every time the file is opened. This will ensure that you always show the user the latest refresh date.

Note: Remember that the Named Query data will only be updated when the System Data dimension is processed. For this purpose I’m using a SSIS package with two tasks, the first processes all the dimensions and the second processes all the cubes. This procedure reduces the chances of errors and ensures always a proper data update.

Advertisements
Tagged with: , , , , ,
Posted in SSAS
2 comments on “Show last cube data update date and time
  1. Lars says:

    Hey
    How you found a way to see the time for the latest update in excel?

    • jbartual says:

      Hi Lars,

      I’ve onlu found the way to show in Excel the latest update time for a SSAS cube. I don’t include Excel as a datasource in my BI solution.

      Thanks,
      Jordi

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Enter your email address to subscribe to this blog and receive notifications of new posts by email.

Join 24 other followers

Categories
%d bloggers like this: