• Post Categories

  • Browse Blogs

  • Blog Stats

    • 475,763 hits
  • Syndications

    SQLServerPedia Contributor

Dynamically generate current Year, Month or Date member with MDX

MDX can be extended with Visual Basic functions like the FORMAT() and NOW() functions to dynamically generate the current year, month or date member. For example, it can be used as part of a named set to generate current year budget and actuals. It can also be used as part of a filter in a Performance Point dashboard.

First, a little review of VB NOW() and FORMAT() function:

NOW()

The NOW() VB function returns the current system date and time. It is similar to the T-SQL GETDATE() function.

FORMAT()

The FORMAT() VB function returns a string formatted according to instructions contained in a format String expression.
Source: http://msdn.microsoft.com/en-us/library/59bz1f0h(v=vs.90).aspx

There is an extensive list of characters you can use to create your own date and time formats. You can see the complete list here: http://msdn.microsoft.com/en-us/library/73ctwf33(v=vs.90).aspx

For example, you can extract the year portion of your date in the following formats:

y: Displays the year number (0-9) without leading zeros. Use %y if this is the only character in your user-defined numeric format.
Example: FORMAT(NOW(), “y”)
Result: 1 (for any date in 2011)

yy: Displays the year in two-digit numeric format with a leading zero, if applicable.
Example: FORMAT(NOW(), “yy”)
Result: 11 (for any date in 2011)

yyy or yyyy: Displays the year in four-digit numeric format.
Example: FORMAT(NOW(), “yyy”) or FORMAT(NOW(),”yyyy”)
Result: 2011 (for any date in 2011)

FORMAT() and NOW() with MDX

The following MDX example uses the Adventure Works 2008R2 Analysis Services OLAP cube. Suppose we need to return all Internet Sales for the year 2011. The MDX script would look something like this:

SELECT [Measures].[Internet Sales Amount] ON COLUMNS
FROM [Adventure Works]
WHERE [Date].[Calendar Year].&[2011]

This works just fine and will return the aggregated sales of 2011. (Note: the Adventure Works 2008R2 does not contain sample sales data for 2011, result will be an Empty dataset).

But what if instead of a specific year, you are asked to return the Internet Sales amount for the current year? You have 3 options:

  1. On January 1st at 12:01 AM every year you need to change your MDX script to the new current year.
    CONS: You might be passed out from celebrating New Year’s Eve and most probably you will not get to it until a few days out into the new current year.
  2. You could use some MDX functions like LasNonEmpty and LastChild to figure out the last amounts recorded by the most recent date.
    CONS: You may have forecast data or simply bad data with transactions occurring on dates out into the future. (Trust me, I’ve seen it)
  3. You can build a dynamic MDX script that will return the current year based on the system datetime.
    CONS: It may be too sexy for your cube.

The trick is to construct the Date dimension member using the STRTOMEMBER MDX function. The STRTOMEMBER MDX function stands for “String to Member” and as the name implies, it will convert a string to a dimension member. In this case, we want to generate the [Date].[Calendar Year].&[2011] dimension member from a string using the Year portion of the system datetime.

The dynamic string will look like this:

StrToMember(“[Date].[Calendar Year].&[“+FORMAT(NOW(), “yyyy”)+”]”)

Now we can use it in our MDX script:

SELECT [Measures].[Internet Sales Amount] ON COLUMNS
FROM [Adventure Works]
WHERE StrToMember(“[Date].[Calendar Year].&[“+Format(now(), “yyyy”)+”]”)

The same applies if you want to select or filter data for the current month. In the following example I’m be using the Month level from the Calendar Hierarchy and specifying the month of July (7) for the year 2011:

SELECT [Measures].[Internet Sales Amount] ON COLUMNS
FROM [Adventure Works]
WHERE [Date].[Calendar].[Month].&[2011]&[7]

We can generate the current month member as follows:

SELECT [Measures].[Internet Sales Amount] ON COLUMNS FROM [Adventure Works] WHERE StrToMember(“[Date].[Calendar].[Month].&[“+Format(now(), “yyyy”)+”]&[“+Format(now(), “M”)+”]”)

Note that to specify the Month portion we use CAPITAL letter M because it is case-sensitive. Smallcase letter m stands for minute. The complete list of characters can be viewed here: http://msdn.microsoft.com/en-us/library/73ctwf33(v=vs.90).aspx

SQL Server Codename “Denali” CTP3 Resources

SQL Server codename “Denali” CTP3 has been released to the public after much anticipation. You may ask yourself, “What happened to CTP2?” The answer is simple. It was a private build for MVPs and some Microsoft partners. By private, I mean it was not released to the general public. The good news is that you did not miss much in CTP2. A lot of areas were incomplete or not working. But that is to be expected as it is a work in progress.

This blog post will serve as a means to gather resources such as links and blog posts regarding SQL Server “Denali” CTP3. Check back soon as I will be adding new resources. If you have a blog post about SQL Server Denali CTP3 please pingback or email me to add it to the list.

Downloads:

SQL Server DENALI CTP3 Demo VHD
http://www.microsoft.com/download/en/details.aspx?id=27253

A HyperV image of SQL Server Denali CTP3 in action, including fully configured services and integration with SharePoint 2010 and Office 2010
The following software is configured on the virtual machine:

  • SQL Server “Denali” CTP3
  • SharePoint 2010
  • Office 2010

SQL Server codename “Denali” Community Technology Preview 3 CTP3
https://www.microsoft.com/betaexperience/pd/SQLDCTP3CTA/enus/default.aspx

SQL Server code name “Denali” Express Core Community Technology Preview 3 (CTP 3)
http://www.microsoft.com/download/en/details.aspx?id=26784

SQL Server code name ‘Denali’ Community Technology Preview 3 (CTP 3) Feature Pack
http://www.microsoft.com/download/en/details.aspx?id=26726

The SQL Server  code name “Denali” CTP 3 Feature Pack is a collection of stand-alone packages which provide additional value for Microsoft® SQL Server® code name ‘Denali’ CTP 3. It includes the latest versions of  tool and components an add-on providers.

Includes:

  • SQL Servercode name “Denali” Master Data Service Add-in for Excel CTP 3
  • SQL Servercode name “Denali” Semantic Language Statistics CTP 3
  • SQL ServerReport Builder for SQL Servercode name “Denali” CTP 3
  • SQL Servercode name “Denali” PowerPivot for  Excel CTP 3
  • SQL Servercode name “Denali” Reporting Services Add-in for  SharePoin Technologies
  • SQL Servercode name “Denali” Data-Tier Application Framework CTP 3
  • SQL Servercode name “Denali” Transact-SQL Language Service CTP 3
  • SQL Servercode name “Denali” Transact-SQL ScriptDom CTP 3
  • SQL Servercode name “Denali” Transact-SQL Compiler Service CTP 3
  • SQL ServerCompact 4.0
  • SQL ServerCompact 4.0 Books On-line
  • SQL ServerJDBC Driver 4.0 Community Technology 2 (CTP 2)
  • Connector 1.1 for SAP BW for SQL Server code name “Denali” CTP 3
  • System CLR Types for SQL Server code name “Denali” CTP 3
  • SQL Servercode name “Denali” Remote Blob Store CTP 3
  • SQL Servercode name “Denali” Books On-line CTP 3
  • SQL Servercode name “Denali” Upgrade Advisor CTP 3
  • SQL Servercode name “Denali” Native Client CTP 3
  • OLEDB Provider for DB2 v4.0 for SQL Server code name “Denali” CTP 3
  • SQL Servercode name “Denali” Command Line Utilities CTP 3
  • SQL ServerService Broker External Activator for SQL Server code name “Denali” CTP 3
  • Windows PowerShell Extensions for SQL Server code name “Denali” CTP 3
  • SQL Servercode name “Denali” Shared Management Objects CTP 3
  • SQL Servercode name “Denali” ADOMD.NET CTP 3
  • Analysis Services OLE DB Provider for SQL Servercode name “Denali” CTP 3
  • SQL Servercode name “Denali” Analysis Management Objects CTP 3
  • SQL ServerDriver for PHP 2.0
  • SQL ServerMigration Assistant
    1. Microsoft SQL Server Migration Assistant for Access
    2. Microsoft SQL Server Migration Assistant for MySQL
    3. Microsoft SQL Server Migration Assistant for Oracle
    4. Microsoft SQL Server Migration Assistant for Sybase
    5.  Microsoft SQL Server Migration Assistant 2008 for Sybase PowerBuilder Applications
  • SQL ServerStreamInsight v1.2

Adventure Works sample databases for SQL Server codename Denali CTP3
http://msftdbprodsamples.codeplex.com/releases/view/55330

Denali CTP3 Adventure Works Sample Databases Readme
http://social.technet.microsoft.com/wiki/contents/articles/sql-server-samples-readme.aspx

Includes:

  • AdventureWorks2008R2 Data File
  • AdventureWorksDWDenali Data File
  • SSAS Multidimensional Model Projects Denali CTP3
  • SSAS Tabular Model Projects Denali CTP3
  • SSAS AMO2Tabular Denali CTP3

Don’t forget to read the sample databases readme file:
http://social.technet.microsoft.com/wiki/contents/articles/sql-server-samples-readme.aspx

Tutorials

Tutorials for SQL Server “Denali”
http://msdn.microsoft.com/en-us/library/hh231699(v=sql.110).aspx

Includes:

  • Multidimensional Modeling (Adventure Works Tutorial)
  • Tabular Modeling (Adventure Works Tutorial)
  • Tutorial for Project Crescent

Blogs / Wikis:

Microsoft SQL Server Code-Named “Denali” CTP3 Release Notes
http://social.technet.microsoft.com/wiki/contents/articles/3711.aspx

SQL Server Team: SQL Server Code Name “Denali” CTP3 and SQL Server 2008 R2 SP1 are HERE!
http://blogs.technet.com/b/dataplatforminsider/archive/2011/07/11/sql-server-code-name-denali-ctp3-is-here.aspx

SSIS Team Blog: Matt Mason – What’s new in SQL Server Denali?
http://blogs.msdn.com/b/mattm/archive/2011/07/12/ssis-what-s-new-in-sql-server-denali.aspx

SQL Server Reporting Services Team Blog: Thierry Dhers – SQL Server codename “Denali” CTP3, including Project “Crescent” is now publically available
http://blogs.msdn.com/b/sqlrsteamblog/archive/2011/07/12/sql-server-codename-quot-denali-quot-ctp3-including-project-quot-crescent-quot-is-now-publically-available.aspx

What’s New in Master Data Services (MDS) in Denali CTP3
http://social.technet.microsoft.com/wiki/contents/articles/3714.aspx

Marco Russo: Installing Analysis Services ssas #Denali CTP3 and PowerPivot Denali CTP3
http://sqlblog.com/blogs/marco_russo/archive/2011/07/13/installing-analysis-services-ssas-denali-ctp3-and-powerpivot-denali-ctp3.aspx

Brent Ozar: CTP3 is Here! Five Things to Know About the Next Version of SQL Server
http://www.brentozar.com/archive/2011/07/five-things-sql-server-denali-ctp3/

Jamie Thompson: SSIS enhancements in Denali CTP3
http://www2.sqlblog.com/blogs/jamie_thomson/archive/2011/07/12/ssis-enhancements-in-denali-ctp3.aspx

MSDN SQL Server (Pre-release) forum threads
http://social.msdn.microsoft.com/Forums/en-US/category/sqlserverprerelease

SQL Server “Deanali” Books Online (BOL)
http://msdn.microsoft.com/en-us/library/ms130214(SQL.110).aspx

Technet Wiki: Project Crescent Overview
http://social.technet.microsoft.com/wiki/contents/articles/project-crescent-overview.aspx

Update Log:
7/26/2011 Added Denali CTP3 Adventure Works Sample Databases Readme link

9/25/2011 Added SQL Server DENALI CTP3 Demo VHD download link and description and Technet Wiki Project Crescent overview.

%d bloggers like this: