Tag Archive for: Analysis Services

Consider the following calculated member definition added to the script in an Analysis Services cube:

CREATE
MEMBER
CURRENTCUBE.[Measures].[Revenue TTM] AS ([Measures].[Revenue Amount USD], [Relative Date].[TTM]), FORMAT_STRING = “Currency”, VISIBLE = 1;

When you browse the member in Excel, you’ll find that the member is not formatted as currency. However, when you use SSMS and query the member, when you double-click the member cell in the query results, you’ll see that the value is formatted as expected. Clearly, Excel has an issue with formatting calculated members and there is a connect bug report that has been quietly sitting in the Excel bucket for years. To Microsoft credit, we have a KB article “Description of the LANGUAGE cell property in SQL Server 2005 Analysis Services”.

“However, SQL Server 2005 Analysis Services cannot determine the value of the LANGUAGE cell property or of the currency symbol for calculated members from the query context. You must specify the LANGUAGE cell property if you want to obtain a nondefault currency symbol. If you do not specify the LANGUAGE cell property, the numeric value of the cell value and the currency symbol of the cell value may not match.”

I think what they meant was specifying the Language property in the calculated member definition. Indeed, the following change causes Excel to show the calculated member formatted as currency (USD in this case):

CREATE
MEMBER
CURRENTCUBE.[Measures].[Revenue TTM] AS ([Measures].[Revenue Amount USD], [Relative Date].[TTM]), FORMAT_STRING = “Currency”, VISIBLE = 1, LANGUAGE=1033;

What about dynamic formatting based on the selected currency? Assuming that you have a Destination Currency dimension and includes a Locale column with the integer locale identifier, e.g. 1033 for USD, 1031 for EUR, and so on), the following script overwrites the member language and the value will be formatted based on the destination currency selected by the user:

Scope (

    [Destination Currency].[Currency].[Currency].Members,

    {

        [Measures].[Revenue TTM]

    }

    );

    Language( this ) = [Destination Currency].[Currency].Properties( “Locale” );

End
Scope;

TIP On the subject of formatting, you might need to “pin” a calculated member to a particular currency format to avoid the OLAP browser changing the format. As a fist attempt, you might try hardcoding the currency sign, such as “$0,0.00′”. Unfortunately, Excel will ignore it and overwrite it based on the regional settings. For example, a UK user will still see “£100.00”. Hrvoje Piasevoli suggested escaping the currency symbol to prevent this from happening, e.g. create member currentcube.measures.test as 1000.01, format_string=”\$0,0.00″, language=1033;

Since I live in the USA, I have to admit I’m somewhat shielded from the localization complexities surrounding currency and date formatting. Chris Webb wrote a blog “Currency formats: should they be tied to language?” a while back that you should check as well.

Today happens to be my birthday. To reverse the tradition, how about I give you a gift? If you find yourself spending hours creating partitions in BIDS, you might find the Partition Generator sample (requires registration at the Prologika website) useful. Often, I partition larger cubes by month which may require creating quite a few partitions. Not only is this time-consuming and tedious, but is also error-prone as you can easily get the partition slice query wrong. Written as a C# console application, Partition Generator has the following design goals:

1. Keep it simple on purpose and due to time constraints. So, no GUI yet and you need to have basic .NET coding skills. You will need Visual Studio 2008 or later with C# to configure it.
2. Auto-generate a partition definition file in an offline mode without making changes to the server by default. You can use this file to replace the *.partitions file in BIDS so the partitioning scheme can become a part of your project.
3. Support an online mode where you can deploy the partition changes to the server.

Configuring Partition Generator

Configuring partition generator requires opening the source and making changes to reflect your environment. Specifically, you need to update the following variables:

static string _server = “<your server>”; // SSAS server where the cube is deployed

static string _database = “<database>”; // SSAS datasbase

static string _cube = “<cube>”; // SSAS cube to partition

static string _measureGroup = “<measure group>”; // Measure group to partition

static string _dataSource = “<data source name>”; // The data source name

static DateTime _dateStart = new DateTime(2007, 1, 1); // start date for the period

static DateTime _dateEnd = new DateTime(2020, 12, 31); // end date for the period

// the partition query

static string _query = “SELECT * FROM [dbo].[FactInternetSales] WHERE OrderDateKey “;

Note that you need a live cube to connect to which can be partitioned or not partitioned (default partition only). The start date and end date variables specify the start date for the first partition and end date for the last partition respectively. By default, Partition Generator will partition by month but you can overwrite this in code by changing the following line:

_dateStart = _dateStart.AddMonths(1); // assuming parititon by month

Partition Manager creates also start and end partitions for dates outside the date range which could be handy if you forget to add partitions after the end date is passed. Partition Generator sets also the partition slice which is a recommended performance-optimization technique.

Updating the Project

Once you configure Partition Generator, run it with Ctrl+F5. It uses AMO to retrieve the cube metadata and generates a partitions.xmla file in the project bin\debug folder.

1. Double-click the partitions.xmla to open it in SQL Server Management Studio.

2. Find and collapse the Partitions XML element. Select the Partitions element and press Ctrl+C to copy its content.

   

    

3. Back up your SSAS project. Open your SSAS project in BIDS. In Solution Explorer, click Show All Files.
4. Expand the Cubes folder, right-click the <cube name>.partitions file and click View Code to open its source.
5. Find and select the entire Partitions element for the measure group you want to partition,  and press Ctrl+V to replace the source with new partition schema which you copied in SSMS.
6. Save the project and close BIDS. That’s because BIDS doesn’t handle well changes made directly to the source files.
7. Open the SSAS project again in BIDS. Open your cube in the Cube Designer and click the Partitions tab. You should see the new partitions.

Deploying Partitions

By default, Partition Generator doesn’t change the deployed cube. It reads metadata only. However, you can be configured to deploy the partition changes. This could be useful when you want to experiment with different partitioning schemes, such as by month, quarter, year, etc. To configure Partition Manager to deploy the changes:

1. In the PartitionGenerator project, right-click the PartitionGenerator project node in Solution Explorer and click Properties.
2. In the Project Properties dialog box, select the Build tab and define a RELEASE conditional compilation symbol as follows:

   

3. Run Partition Manager to generate both the partitions.xmla file and apply the changes to the SSAS server.

I haven’t tested Partition Manager with BISM Tabular. It should work since Tabular uses the same Partition element in the model (*.bim) file. One more thing – Partition Manager is not supported so please use it at your own risk.

Enjoy!