If there is one thing I have learned during my career, it’s that managers and accountants love rolling totals and averages.

The needs might differ slightly depending on the type of business, an accountant or a financial controller is probably interested in a rolling total showing closed results only which in many cases means comparing previous month and months preceding it. A lot of retail operations close sales books every day, and someone monitoring sales is therefore interested in comparing yesterday’s results with the days preceding.

To retrieve data sets containing data for previous periods relative to today, we need to obtain the current date and this is surprisingly complicated if you are using MDX. One way to do it is by invoking VBA functions and parsing strings, but a better way to retrieve the current date is to simply create a named set that stores current date as suggested by Mosha Pashumansky.  We can then create a C# executable or SSIS script task that updates the named set to today’s date. To create a simple executable using AMO:

1. Open Visual Studio 2010 and create a console project.

2. Add references to System.Configuration and Microsoft.AnalysisServices (found in C:\Program Files\Microsoft SQL Server\100\SDK\Assemblies for MS SQL 2008 R2) to your project.

3. Add the following namespaces to your Program.cs file:

using System.Globalization;

using Microsoft.AnalysisServices;

using System.Globalization;

4. Open the cube in  your SSAS solution, click the calculations tab, then click the  Script View tab and copy the entire MDX script.

5. Add a text file to  your console project and paste the MDX script into this file.

6.  Use the following code snippet to create the named set:

string connectionString = ConfigurationManager.ConnectionStrings["adw"].ConnectionString;

Server asServer = new Server();

asServer.Connect(connectionString);

Database adw = asServer.Databases.GetByName(ConfigurationManager.AppSettings["db"]); // this throws exception if the database is not found

Cube cb = adw.Cubes[0];

//CultureInfo helps us override local date format settings:

CultureInfo ci = new CultureInfo(”en-US”, false);

System.Threading. Thread.CurrentThread.CurrentCulture = ci;

DateTime time = DateTime.Now; // Use current time
string format = ”MMMM d, yyyy”;

Console.WriteLine(time.ToString(format));

//Retrieve cube mdx script:
MdxScript first = cb.MdxScripts[0];

//Read script minus [Today]
string text = System.IO. File.ReadAllText(@”C:\MDXScript.txt”);

//text = text + ”Create Set CurrentCube.[Today] As {[Date].[Date].[June 1, 2002]};”;
//Append [Today]:
text = text + ”Create Set CurrentCube.[Today] As {[Date].[Date].[" + time.ToString(format) + "]}”;

//Replace cube mdx script with the parsed script.
first.Commands[0].Text = text;

first.Update();

cb.Update();
adw.Update();

cb.Process();

Console.WriteLine(”Processing started”);

adw.Process();

asServer.Dispose();

Console.WriteLine(”processing finished”);

Console.ReadLine();

The above code could also be run inside an SSIS script component.The problem with Adventure Works is that the time dimension data is a couple of years old, so for demo purposes you want to use a hard coded date instead of the current date.

We can now use the [Today] named set to retrieve rolling averages and totals. The query below let’s us look at the reseller sales” for the last 100 days ending with yesterday. Note the use of the Lag function which retrieves yesterday’s date:

select [Measures].[Reseller Sales Amount] on 0,
LastPeriods(100,[Today].Item(0).Lag(1)) on 1
from [Adventure Works]

The current date named set can also be used together with the  ParallellPeriod function, but that’s another post.