通过

Finding the Last Processed Date for Analysis Services Cubes, Measure Groups and Partitions

  • 项目

Recently, a question was raised on one of the email aliases that I follow and I’ve seen the same basic question raised multiple times on various news groups. Initially, the question was “Where or how can I find the Last Processed Date for the cubes contained in an Analysis Services database?” As database sizes have increased, resulting in increased use of partitioning in Analysis Services cubes, that question has morphed to something closer to “Where or how can I find the Last Processed Date for the cubes and partitions contained in an Analysis Services database?”

For the original question, the standard answer was to execute something like the following Dynamic Management View (DMV) query:

SELECT * FROM $SYSTEM.MDSCHEMA_CUBES

When executed in the context of the Adventure Works Multidimensional database, the following result is returned:

MD_MDSCHEMA_CUBES

When executed in the context of the tabular version of the Adventure Works database, the following (and interestingly very similar) result set is returned:

TAB_MDSCHEMA_CUBES

If something a bit less verbose is desired, then a DMV query similar to the following is pretty nice:

SELECT [CATALOG_NAME], CUBE_NAME, LAST_DATA_UPDATE FROM $System.MDSCHEMA_CUBES order by last_data_update DESC

Executed against the multidimensional database, the following data set is returned.

MD_MDSCHEMA_CUBESII

The same DMV query executed against the tabular version of Adventure Works returns the following partial data set.

TAB_MDSCHEMA_CUBESII

The DMV queries produce a result indicating the last date that the cube was processed, but there are a couple of problems with this approach. Regardless of whether the model is Multidimensional or Tabular, the MDSCHEMA_CUBES DMV returns data on when the various dimensions included in the cube. For example, we get information related to the Reseller dimension (CUBE_NAME = $Reseller), Promotion Dimension (CUBE_NAME = $Promotion), the Employee Dimension (CUBE_NAME=$Employee) and the Organization Dimension (CUBE_NAME=$Organization. It’s possible to eliminate the dimensions from the result by changing the DMV query to something similar to the following:

SELECT [CATALOG_NAME], CUBE_NAME, LAST_DATA_UPDATE FROM $System.MDSCHEMA_CUBES where LEFT([CUBE_NAME],1) <>'$' order by last_data_update DESC

Executed against the multidimensional version of the Adventure Works 2012 database, we get the following:

MD_MDSCHEMA_CUBESIII

When the same query is executed against the Tabular version of the Adventure Works database, the following result is returned:

TAB_MDSCHEMA_CUBESIII

This surfaces another problem with querying the MDSCHEMA_CUBES DMV, since all tables in a tabular model are both dimensions and fact tables. For example, the Internet Sales table contains measures and is a partitioned table.

In order to get any information regarding the Last Processed Date below the level of the cube, it’s really necessary to invoke the AMO API. To those unaccustomed to writing a bit of VB, C#, or PowerShell this may sound somewhat daunting but it’s actually pretty trivial to write the code to return the Last Processed Date for the Cube, Measure Groups, and Partitions.

Writing in C#, the following code will work quite nicely to build out a console application in C#:

using System;

using System.Collections.Generic;

using System.Linq;

using System.Text;

using System.Threading.Tasks;

using Microsoft.AnalysisServices;

 

namespace GetProcessedDate

{

class Program

{

static void Main(string[] args)

{

Server AsServer = new Server();

AsServer.Connect(string.Format("Data Source={0}",args[0].ToString()));

Database AsDB = AsServer.Databases.FindByName(args[1].ToString().Trim());

foreach (Cube cub in AsDB.Cubes)

{

Console.WriteLine(string.Format("Cube Name = {0}, Last Processed Date = {1}", cub.Name.ToString(), cub.LastProcessed.ToString()));

foreach (MeasureGroup mg in cub.MeasureGroups)

{

foreach (Partition part in mg.Partitions)

{

Console.WriteLine(string.Format("Measure Group = {0}, Partition = {1}, Last Processed Date = {2}", mg.Name.ToString(), part.ToString(), part.LastProcessed.ToString()));

}

}

}

AsServer.Disconnect();

AsServer.Dispose();

Console.ReadLine();

}

}

}

 

It’s possible to do the same thing and obtain identical output using fewer lines of code with PowerShell, as in the example below:

Param(

[Parameter(Mandatory=$True)]

[string]$ServerName,

[Parameter(Mandatory=$True)]

[string]$DataBaseName

)

 

[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.AnalysisServices") >$NULL

$server = New-Object Microsoft.AnalysisServices.Server

$server.connect($ServerName.ToString())

$database = $server.databases

$asdb = $database[$DatabaseName.ToString()]

foreach ($cube in $asdb.Cubes)

{

Write-Host "Cube Name = $($cube.name) , Last Processed Date = $($cube.LastProcessed)"

foreach ($measuregroup in $cube.MeasureGroups)

{

foreach ($part in $measuregroup.Partitions)

{

Write-Host "MeasureGroup Name = $($measuregroup.name) , Partition Name = $($part.name) , Last Processed Date = $($part.LastProcessed)"}}

}

 

 

When executed against a multidimensional model, both return the following data:

Cube Name = Adventure Works , Last Processed Date = 07/22/2016 16:15:47

MeasureGroup Name = Internet Sales , Partition Name = Internet_Sales_2005 , Last Processed Date = 07/22/2016 16:15:40

MeasureGroup Name = Internet Sales , Partition Name = Internet_Sales_2006 , Last Processed Date = 07/22/2016 16:15:41

MeasureGroup Name = Internet Sales , Partition Name = Internet_Sales_2007 , Last Processed Date = 07/22/2016 16:15:42

MeasureGroup Name = Internet Sales , Partition Name = Internet_Sales_2008 , Last Processed Date = 07/22/2016 16:15:42

MeasureGroup Name = Internet Orders , Partition Name = Internet_Orders_2005 , Last Processed Date = 07/22/2016 16:15:38

MeasureGroup Name = Internet Orders , Partition Name = Internet_Orders_2006 , Last Processed Date = 07/22/2016 16:15:42

MeasureGroup Name = Internet Orders , Partition Name = Internet_Orders_2007 , Last Processed Date = 07/22/2016 16:15:45

MeasureGroup Name = Internet Orders , Partition Name = Internet_Orders_2008 , Last Processed Date = 07/22/2016 16:15:42

MeasureGroup Name = Internet Customers , Partition Name = Customers_2005 , Last Processed Date = 07/22/2016 16:15:38

MeasureGroup Name = Internet Customers , Partition Name = Customers_2006 , Last Processed Date = 07/22/2016 16:15:41

MeasureGroup Name = Internet Customers , Partition Name = Customers_2007 , Last Processed Date = 07/22/2016 16:15:42

MeasureGroup Name = Internet Customers , Partition Name = Customers_2008 , Last Processed Date = 07/22/2016 16:15:44

MeasureGroup Name = Sales Reasons , Partition Name = Internet_Sales_Reasons , Last Processed Date = 07/22/2016 16:15:37

MeasureGroup Name = Reseller Sales , Partition Name = Reseller_Sales_2005 , Last Processed Date = 07/22/2016 16:15:45

MeasureGroup Name = Reseller Sales , Partition Name = Reseller_Sales_2006 , Last Processed Date = 07/22/2016 16:15:43

MeasureGroup Name = Reseller Sales , Partition Name = Reseller_Sales_2007 , Last Processed Date = 07/22/2016 16:15:44

MeasureGroup Name = Reseller Sales , Partition Name = Reseller_Sales_2008 , Last Processed Date = 07/22/2016 16:15:46

MeasureGroup Name = Reseller Orders , Partition Name = Reseller_Orders_2005 , Last Processed Date = 07/22/2016 16:15:46

MeasureGroup Name = Reseller Orders , Partition Name = Reseller_Orders_2006 , Last Processed Date = 07/22/2016 16:15:43

MeasureGroup Name = Reseller Orders , Partition Name = Reseller_Orders_2007 , Last Processed Date = 07/22/2016 16:15:45

MeasureGroup Name = Reseller Orders , Partition Name = Reseller_Orders_2008 , Last Processed Date = 07/22/2016 16:15:46

MeasureGroup Name = Sales Summary , Partition Name = Total_Sales_2005 , Last Processed Date = 07/22/2016 16:15:43

MeasureGroup Name = Sales Summary , Partition Name = Total_Sales_2006 , Last Processed Date = 07/22/2016 16:15:45

MeasureGroup Name = Sales Summary , Partition Name = Total_Sales_2007 , Last Processed Date = 07/22/2016 16:15:45

MeasureGroup Name = Sales Summary , Partition Name = Total_Sales_2008 , Last Processed Date = 07/22/2016 16:15:46

MeasureGroup Name = Sales Orders , Partition Name = Total_Orders_2005 , Last Processed Date = 07/22/2016 16:15:43

MeasureGroup Name = Sales Orders , Partition Name = Total_Orders_2006 , Last Processed Date = 07/22/2016 16:15:45

MeasureGroup Name = Sales Orders , Partition Name = Total_Orders_2007 , Last Processed Date = 07/22/2016 16:15:46

MeasureGroup Name = Sales Orders , Partition Name = Total_Orders_2008 , Last Processed Date = 07/22/2016 16:15:45

MeasureGroup Name = Sales Targets , Partition Name = Sales_Quotas , Last Processed Date = 07/22/2016 16:15:37

MeasureGroup Name = Financial Reporting , Partition Name = Finance , Last Processed Date = 07/22/2016 16:15:40

MeasureGroup Name = Exchange Rates , Partition Name = Currency_Rates , Last Processed Date = 07/22/2016 16:15:39

Cube Name = Mined Customers , Last Processed Date = 07/22/2016 16:15:48

 

When executed with the Tabular version of the Adventure Works database, both return the following data set.

Cube Name = Model , Last Processed Date = 04/03/2015 11:11:37

MeasureGroup Name = Currency , Partition Name = Currency , Last Processed Date = 04/03/2015 11:11:19

MeasureGroup Name = Customer , Partition Name = Customer , Last Processed Date = 04/03/2015 11:11:19

MeasureGroup Name = Date , Partition Name = Date , Last Processed Date = 04/03/2015 11:11:20

MeasureGroup Name = Employee , Partition Name = Employee , Last Processed Date = 04/03/2015 11:11:20

MeasureGroup Name = Geography , Partition Name = Geography , Last Processed Date = 04/03/2015 11:11:18

MeasureGroup Name = Product , Partition Name = Product , Last Processed Date = 04/03/2015 11:11:19

MeasureGroup Name = Product Category , Partition Name = Product Category , Last Processed Date = 04/03/2015 11:11:19

MeasureGroup Name = Product Subcategory , Partition Name = Product Subcategory , Last Processed Date = 04/03/2015 11:11:19

MeasureGroup Name = Promotion , Partition Name = Promotion , Last Processed Date = 04/03/2015 11:11:19

MeasureGroup Name = Reseller , Partition Name = Reseller , Last Processed Date = 04/03/2015 11:11:17

MeasureGroup Name = Sales Territory , Partition Name = Sales Territory , Last Processed Date = 04/03/2015 11:11:20

MeasureGroup Name = Internet Sales , Partition Name = Internet Sales 2005 , Last Processed Date = 09/14/2016 17:29:51

MeasureGroup Name = Internet Sales , Partition Name = Internet Sales 2006 , Last Processed Date = 09/14/2016 17:29:54

MeasureGroup Name = Internet Sales , Partition Name = Internet Sales 2007 , Last Processed Date = 09/14/2016 17:29:58

MeasureGroup Name = Internet Sales , Partition Name = Internet Sales 2008 , Last Processed Date = 09/14/2016 17:30:03

MeasureGroup Name = Internet Sales , Partition Name = Internet Sales 2009 , Last Processed Date = 09/14/2016 17:30:06

MeasureGroup Name = Internet Sales , Partition Name = Internet Sales 2010 , Last Processed Date = 09/14/2016 17:30:09

MeasureGroup Name = Product Inventory , Partition Name = Product Inventory 2005 , Last Processed Date = 04/03/2015 11:11:18

MeasureGroup Name = Product Inventory , Partition Name = Product Inventory 2006 , Last Processed Date = 04/03/2015 11:11:21

MeasureGroup Name = Product Inventory , Partition Name = Product Inventory 2007 , Last Processed Date = 04/03/2015 11:11:25

MeasureGroup Name = Product Inventory , Partition Name = Product Inventory 2008 , Last Processed Date = 04/03/2015 11:11:28

MeasureGroup Name = Product Inventory , Partition Name = Product Inventory 2009 , Last Processed Date = 04/03/2015 11:11:31

MeasureGroup Name = Product Inventory , Partition Name = Product Inventory 2010 , Last Processed Date = 04/03/2015 11:11:34

MeasureGroup Name = Reseller Sales , Partition Name = Reseller Sales 2005 , Last Processed Date = 04/03/2015 11:11:18

MeasureGroup Name = Reseller Sales , Partition Name = Reseller Sales 2006 , Last Processed Date = 04/03/2015 11:11:22

MeasureGroup Name = Reseller Sales , Partition Name = Reseller Sales 2007 , Last Processed Date = 04/03/2015 11:11:24

MeasureGroup Name = Reseller Sales , Partition Name = Reseller Sales 2008 , Last Processed Date = 04/03/2015 11:11:27

MeasureGroup Name = Reseller Sales , Partition Name = Reseller Sales 2009 , Last Processed Date = 04/03/2015 11:11:30

MeasureGroup Name = Reseller Sales , Partition Name = Reseller Sales 2010 , Last Processed Date = 04/03/2015 11:11:33

MeasureGroup Name = Sales Quota , Partition Name = Sales Quota , Last Processed Date = 04/03/2015 11:11:17