Display Folders in Tabular

Update: since writing this post, BIDS Helper now supports display folders for tabular models.

I have never liked the way Excel groups measures by measure groups.  Measure groups often mean nothing to end users; grouping by business function makes a lot more sense to the users.  What I have done in the past with Multidimensional is to only expose measures as calculated measures – associated with a pseudo measure group called something like “Measures” – and then use display folders to group by business function.

The tabular model designer in SSDT doesn’t support display folders.  Display folders will likely be supported in a future release – if tools like BIDS Helper don’t get there first.

Cathy Dumas posted about this here.  The workaround she suggested will work for most implementations.

But what if you really need display folders?  For example, you may need multi-level folders – or maybe it’s just what your users have come to expect having used multidimensional cubes.  While the tabular model designer does not support display folders, you can set them using AMO.  I don’t think we are breaking any rules here; AMO is after all a supported interface.  This (non-production quality) sample code takes a mapping file and sets the display folders.  You can integrate it into your deployment process.

Mapping file sample:

//Measure|Display Folder
[Internet Current Quarter Gross Profit]|Display Folder 1\Level 1
[Internet Current Quarter Gross Profit Performance]|Display Folder 1\Level 1
[Internet Current Quarter Sales]|Display Folder 2\Level 1
[Internet Current Quarter Sales Performance]|Display Folder 1\Level 1;Display Folder 2\Level 1

Sample code:

using System;
using System.IO;
using Microsoft.AnalysisServices;

namespace ConsoleApplication1
{
    class Program
    {
        static void Main(string[] args)
        {
            //to receive as params ...
            string serverName = "localhost";
            string dbName = "AdventureWorks";
            string mappingFile = "..\\..\\MappingFile.txt";

            SetDisplayFolders(serverName, dbName, mappingFile);

            Console.WriteLine();
            Console.WriteLine("Finished.  Hit return to exit.");
            Console.ReadLine();
        }

        private static void SetDisplayFolders(string serverName, string dbName, string mappingFile)
        {
            try
            {
                Console.WriteLine("Setting display folders ...");
                Connect(serverName, dbName);

                using (StreamReader sr = new StreamReader(mappingFile))
                {
                    while (sr.Peek() >= 0)
                    {
                        string line = sr.ReadLine();
                        if (!line.StartsWith("//"))
                        {
                            string[] elements = line.Split('|');

                            if (elements.Length > 1)
                            {
                                if (!_db.Cubes[0].MdxScripts["MdxScript"].CalculationProperties.Contains(elements[0]))
                                {
                                    Console.WriteLine("Warning: CalculationProperty element for following measure not found.  Will create it here, but this suggests that either a) the measure is missing in the model, or b) someone forgot to set the format for this publicly visible measure.");
                                    Console.WriteLine("   " + elements[0]);
                                    _db.Cubes[0].MdxScripts["MdxScript"].CalculationProperties.Add(elements[0]);
                                }

                                _db.Cubes[0].MdxScripts["MdxScript"].CalculationProperties[elements[0]].DisplayFolder = elements[1];
                            }
                        }
                    }
                }

                _db.Update(UpdateOptions.ExpandFull);
                Console.WriteLine("Finished setting display folders for db " + dbName);
            }
            catch (Exception exc)
            {
                Console.WriteLine("Exception: " + exc.Message);
            }
            finally
            {
                Disconnect();
            }
        }

        private static Server _server;
        private static Database _db;

        private static bool Connect(string serverName, string dbName)
        {
            _server = new Server();
            _db = null;
            _server.Connect("DATA SOURCE=" + serverName);

            _db = _server.Databases.FindByName(dbName);
            if (_db == null)
            {
                throw new ConnectionException("Could not connect to database " + dbName);
            }
            Console.WriteLine("Connected to database " + dbName);

            return true;
        }

        private static void Disconnect()
        {
            if (_server != null) _server.Disconnect();
        }

    }
}
Advertisements

CurrentMember vs. HASONEVALUE

One of the most common uses for CurrentMember in MDX is to check if at the all level of a hierarchy. For example …

with member measures.x as
iif([Product].[Product].CurrentMember is [Product].[Product].[All Products],
    'do not apply special calculation',
    'apply special calculation because filtered by a product'
   )
select
measures.x on 0,
[Date].[Calendar].[Calendar Year].Members on 1
from [Adventure Works]
--where [Product].[Product].&[555] //slice by a product - changes evaluation in iif

Here is the DAX equivalent.  Suffice to say that when “x” is returned in the resultset, all products are in filter context (equivalent to the all member in MDX) .  But when “y” is returned, only one product (Thin-Jam Hex Nut 3) is in filter context.

define
measure
    'Date'[x] = if( hasonevalue('Product'[Product Name]),
                    "apply special calculation because filtered by a product",
                    "do not apply special calculation"
                  )
measure
    'Date'[y] = calculate([x], 'Product'[Product Name] = "Thin-Jam Hex Nut 3")
evaluate
addcolumns
(
   values('Date'[Calendar Year])
  ,"x", [x]
  ,"y", [y]
)

Returns

To prove that HASONEVALUE does what it says on the tin …

define
measure
    'Date'[x] = countrows(values('Product'[Product Name]))
measure
    'Date'[y] = calculate([x], 'Product'[Product Name] = "Thin-Jam Hex Nut 3")
measure
    'Date'[z] = calculate([x], 'Product'[Product Name] = "Thin-Jam Hex Nut 3" ||
                               'Product'[Product Name] = "Bearing Ball")
evaluate
addcolumns
(
   values('Date'[Calendar Year])
  ,"x", [x]
  ,"y", [y]
  ,"z", [z]
)

Returns

So “y” indeed has one value.

But what about “z” above? It has 2 values. The MDX equivalent of this is a set of products in the where clause – which CurrentMember can’t handle. And this is one of DAX’s strengths over MDX: we can deal with multi-value filters more easily.

Consider this scenario: apply a conditional calculation only if the user has filtered on one or more products (but not if there is no product filter at all).

HASONEVALUE of course won’t deal with it.

define
measure
    'Date'[x] = if( hasonevalue('Product'[Product Name]),
                    "apply special calculation because filtered by product(s)",
                    "do not apply special calculation"
                  )
measure
    'Date'[y] = calculate([x], 'Product'[Product Name] = "Thin-Jam Hex Nut 3")
measure
    'Date'[z] = calculate([x], 'Product'[Product Name] = "Thin-Jam Hex Nut 3" ||
                               'Product'[Product Name] = "Bearing Ball")
evaluate
addcolumns
(
   values('Date'[Calendar Year])
  ,"x", [x]
  ,"y", [y]
  ,"z", [z]
)

Returns

As “z” has 2 values in filter context, we miss it.

The ISFILTER function would catch it if we only care about filters directly on the ‘Product'[Product Name] column.

define
measure
 'Date'[x] = if( isfiltered('Product'[Product Name]),
 "apply special calculation because filtered by product(s)",
 "do not apply special calculation"
 )
measure
 'Date'[y] = calculate([x], 'Product'[Product Name] = "Thin-Jam Hex Nut 3")
measure
 'Date'[z] = calculate([x], 'Product'[Product Name] = "Thin-Jam Hex Nut 3" ||
 'Product'[Product Name] = "Bearing Ball")
evaluate
addcolumns
(
 values('Date'[Calendar Year])
 ,"x", [x]
 ,"y", [y]
 ,"z", [z]
)

Returns

But if the table can be filtered by other columns, we would have to check them all individually with ISFILTERED, or it won’t detect the filter.

define
measure
 'Date'[x] = if( isfiltered('Product'[Product Name]),
 "apply special calculation because filtered by product(s)",
 "do not apply special calculation"
 )
measure
 'Date'[y] = calculate([x], 'Product'[Model Name] = "Chain")
evaluate
addcolumns
(
 values('Date'[Calendar Year])
 ,"x", [x]
 ,"y", [y]
)

Returns

To detect a filter even if applied on another column on the same table, we can use ISCROSSFILTERED.

define
measure
 'Date'[x] = if( iscrossfiltered('Product'[Product Name]),
 "apply special calculation because filtered by product(s)",
 "do not apply special calculation"
 )
measure
 'Date'[y] = calculate([x], 'Product'[Model Name] = "Chain")
evaluate
addcolumns
(
 values('Date'[Calendar Year])
 ,"x", [x]
 ,"y", [y]
)

Even a parent related table’s column filter is detected – that would result in a single or a multi-value filter.  In the following query, the sub-category “Bike Racks” only has one product, whereas “Touring Frames” has 18.

define
measure
 'Date'[x] = if( iscrossfiltered('Product'[Product Name]),
 "apply special calculation because filtered by product(s)",
 "do not apply special calculation"
 )
measure
 'Date'[y] = calculate([x], 'Product Subcategory'[Product SubCategory Name] = "Bike Racks")
measure
 'Date'[z] = calculate([x], 'Product Subcategory'[Product SubCategory Name] = "Touring Frames")
evaluate
addcolumns
(
 values('Date'[Calendar Year])
 ,"x", [x]
 ,"y", [y]
 ,"z", [z]
)

In both cases, the filter is detected.

Currency Conversion in Analysis Services Multidimensional

I wrote this article on my old blog in August 2006 for SSAS 2005, but everything still applies today even for SSAS 2012 in Multidimensional mode.

Update August 17 2013: see revised version for SSAS 2012.

First thing to do: figure out what type of currency conversion you need.  There are 3 types.

  1. MANY-TO-MANY.  This is where the facts are stored in multiple currencies – i.e. amounts for the same measure are in various currencies in the same fact table.  Also, the users might want to report the total amount in different currencies.
  2. MANY-TO-ONE.  The facts are stored in multiple currencies, but this time it is only necessary to ever report the total amount in a single currency – e.g. a corporate currency.
  3. ONE-TO-MANY.   The facts are always stored in one currency only.  However, the users might want to report the total amount in different currencies.

This post focuses on the different options available for One-to-Many.  With One-to-Many, there are 2 options: measure expressions or the currency conversion wizard.

If you create a DSV on top of AdventureWorksDW and include DimTime, FactInternetSales, FactCurrencyRate and DimCurrency, it should look something like this.

The relationship between FactInternetSales and DimCurrency is for the case where your facts are stored in multiple currencies (Many-to-Many and Many-to-One).  To look at One-to-Many, we will delete this relationship.  Also, we will delete the ShipDate and DueDate relationships between FactInternetSales and DimTime, but leave the OrderDate relationship (this post is about currency conversion, not role-playing dimensions!).

We now have the following DSV diagram.

ONE-TO-MANY USING MEASURE EXPRESSIONS

I created a cube on top of this DSV.  Here is a backup of ithttp://blogs.conchango.com/christianwade/CurrencyConversionBackup.zip  You can download, restore and open it using the VisualStudio using the File > Open > Analysis Services Database menu option.

The only measures in it are Sales Amount (from FactInternetSales) and End Of Day Rate (from FactCurrencyRate).  Sales Amount has a FormatString of “Currency”.  End Of Day Rate has an AggregateFunction property value of “LastNonEmpty”.  LastNonEmpty is a semi-additive measure.  We want it to sum for all dimensions except Date.  For the Date dimension, it will take the last non-empty child.  For example, the exchange rate we would want to see for a week member in a report would not be the sum of the exchange rate on Monday + exchange rate on Tuesday + exchange rate on Wednesday, …  Instead, we just want the most recent exchange rate (maybe exchange rate on Saturday).

The Date dimension is very simple.  The Date attribute, which is the key attribute, has a KeyColumns property of TimeKey (which is the surrogate key of the DimTime table) and a NameColumn of FullDateAlternateKey (what the users see at Date level).

I used the Dimension Wizard to create the Date dimension so that it would be flagged with Type = Time, etc.  This is one of the few cases where having these properties set correctly actually affects cube behaviour.  For example, semi-additive measures and some MDX functions like YTD won’t work without Type = Time.

Here are the mappings in the Dimension Usage tab.  They are pretty straightforward.  There is a many-to-many relationship between Currency and Fact Internet Sales.

Here is a screenshot of the Currency dimension.  The main points about the Currency dimension are as follows.

  • The KeyColumns property of the Currency attribute is set to CurrencyKey (which is the surrogate key in for the DimCurrency table in AdventureWorksDW).
  • The NameColumn property of the Currency attribute is set to CurrencyName (which is what we want the users to see).
  • The Type property of the dimension is set to Currency.  The only purpose of this (unlike Type=Time that does affect cube behaviour) is to inform client tools in case they want to display currency dimensions differently to regular dimensions.
  • The Type property of the Currency attribute is set to CurrencyName.  Again, this is just to inform client tools.
  • The IsAggregatable property of the Currency attribute is set to False.  This removes the All level for the attribute. We would after all not want to sum the values of different currencies.  500 pounds sterling plus 100,000 cruzeiros equals 100,500 of what currency?  Monopoly money?
  • Set the DefaultMember property of the Currency attribute to whatever the Sales Amount values are stored in.  In the case of AdventureWorksDW, it is US Dollars.
  • The attribute relationship between Currency and Currency Alternate Key has its Cardinality property set to One. This is because, for a particular Currency, there can be only one Currency Alternate Key – i.e. they have a one-to-one relationship.  This improves efficiency in aggregation because the numbers will always be the same (e.g. £500 for “Great Britain Pounds” will always result in £500 for “GBP”).  Analysis Services will therefore not bother figuring out the aggregate values for Currency Alternate Key.  It will simply re-use those of Currency.
  • Set the AttributeHierarchyEnabled property of the Currency Alternate Key attribute to False.  If it is not set, Analysis Services will not allow deployment of the project because of the attribute relationship with Currency and that Currency has IsAggregatable = False.

Here is the measure expressions bit.  The Sales Amount measure has the following measure expression: “[Sales Amount] / [End Of Day Rate]”.  The facts are stored in US Dollars and the “pivot currency” is US Dollars.  The pivot currency is the currency the exchange rate values convert from.  The measure expression is a division rather than a multiplication because this is the way the exchange rates are held.  For example, the exchange rate for GBP is roughly 1.5 in the FactCurrencyRate table.  Therefore, to convert $15 from the pivot currency to GBP, 15 / 1.5 gives us 10.  Doing a multiplication would result in £22.50 (obviously wrong).  Note: measure expressions are done at leaf level.

It is worth going back into the Dimension Usage tab and setting the DirectSlice property of the many-to-many relationship.  Setting it to “([Currency].[Currency].&[100])” means that, when querying Sales Amounts by the base/pivot currency (US Dollars), Analysis Services will just return the value as it appears in the fact table – i.e. without applying the measure expression.  After all, there is no need to convert US Dollars into US Dollars!  If we did not set DirectSlice, we would have to ensure that the FactCurrencyRate table has an exchange rate of 1 for converting US Dollars into US Dollars (and for every single day for which we have data).  Otherwise, Analysis Services would be looking for an End Of Day Rate to divide by, find null and return null.  It should also perform better when querying by USD with DirectSlice populated correctly because it doesn’t have to bother with the conversion calculation at all.  So what we want is just a tuple with the default member for every enabled attribute in the outer many-to-many dimension.  Incidentally, this is what theRoot(<dimension_expression>) function would return, but we can’t use any MDX functions in the DirectSlice property (hence the Functions pane is disabled), so we have to explicitly list each default member in a tuple.

Lastly, we will insert some MDX in the Calculations tab that sets the Locale ID for currencies.  This will avoid displaying 500 pounds sterling as “$500” (goodness gracious!).  For a complete list of the Locale IDs, see http://www.microsoft.com/globaldev/reference/lcid-all.mspx

Language([Currency].[Currency].[United Kingdom Pound]) = 2057;
Language([Currency].[Currency].[Brazilian Real]) = 1046;
Language([Currency].[Currency].[US Dollar]) = 1033;

These assignments are for illustration purposes only.  The main problem with this approach is maintainability.  If we bring in new currencies in the future, we need to modify the MDX script.  The ideal would be to store them in the DimCurrency table, expose them using the ValueColumn property of the Currency attribute, and use that for the assignment.  Some may prefer to go the member property route, but I think this is a good use case for the ValueColumn property.

Language(([Currency].[Currency].Members, [Measures].[Sales Amount])) =
[Dim Currency].[Currency].CurrentMember.MemberValue;

Let’s build and deploy!  Now let’s browse!

On the face of it, these numbers seem OK.  29,358,677 is roughly 1.5 times 19,685,311.  But let’s double check. Copying and pasting into Excel reveals that this is not the case.  There is actually a discrepancy of £784,195!  Why is that?  Well, this is actually the behaviour that we want …

If you remember, measure expressions are done at leaf level.  This calculation is, as George Spofford would say, “non-commutative”.  Addition/subtraction combined with multiplication/division will result in different numbers depending on whether the multiplication/division is done at leaf level or the aggregated level.  Analysis Services has done the currency conversion at leaf level – i.e. at the day that each transaction actually took place.  This is of course much more accurate.

So let’s test this as well.  Drilling into Date reveals the following.

Copying and pasting the Date-level data into Excel and applying the multiplication reveals that the numbers are indeed calculated correctly at leaf level.

For one-to-many currency conversion, I prefer using measure expressions rather than the currency conversion wizard.  As shown by this post – http://blogs.conchango.com/christianwade/archive/2006/07/25/4256.aspx  – measure expressions are the most performant way of doing this kind of calculation.  The wizard generates MDX that does the calculation at runtime, so it is not quite as performant for large volumes.  Furthermore, virtually all the work we have just gone through to enable measure-expression currency conversion would also have to be done prior to running the wizard (with the exception of the measure expression itself).  Anyway, for completeness, let’s take a look at how the currency conversion wizard does it.

ONE-TO-MANY USING CURRENCY CONVERSION WIZARD

Picking up where we left off, just delete the measure expression and run the wizard.  As I said, pretty much all the other stuff we had to do to enable measure expressions has to be done prior to running the wizard anyway.

From the Cube Structure tab, select Cube > Add Business Intelligence.

Select “Define Currency Conversion” and click Next.

Fill in the next page in the wizard as shown here.  Specify which measure group contains the exchange rates.  The pivot currency, as discussed above, is US Dollars.  “1.5 US Dollars per 1 United Kingdom Pound” sounds right, so let’s go with that.

Select Sales Amount as the measure(s) we want to convert.

Specify One-to-many currency conversion.

Now it wants us to specify the “reporting currencies”.  It will actually create another currency dimension called “Reporting Currency”, which will be based on this selection.  I would imagine that most cubes that only need one-to-many currency conversion will have the base Currency dimension purely for the purpose of viewing the values in foreign currencies! Adding another currency dimension can be a little overkill in my opinion.  I selected United Kingdom Pound and Brazilian Real for my reporting currencies.

The last page shows us what changes will be made to the cube/DSV.  Click Finish.

Let’s take a look at what it actually did.  Firstly, we have a new entity in our DSV called Reporting Currency.  It is basically the same as the FactCurrencyRate, but filtered on the pivot currency and the other currencies we selected as reporting currencies.  Interestingly, it is not related to any other entity in the DSV.

The wizard also created a dimension called Reporting Currency that is based on the Reporting Currency entity in the DSV.  It is very similar to our Currency dimension.  The Currency attribute has a DefaultMember property of US Dolllars, its IsAggregatable property is set to False, etc., etc.  In fact the only difference of any significance is that it has not set the Cardinality property of the attribute relationship to One.

And here is the MDX script it generated.  Note: there is a bug in this script.  If you are using a Date dimension that is called something different to the base dimension name (e.g. Order Date vs. Date), you will have to replace “Scope( Leaves([Date])” with “Scope( Leaves([Order Date])”.

     // <Currency conversion>
            // Currency conversion wizard generated script.      
            // Currency conversion generated on: 24 August 2006 15:36:38
            // by user: Conchango2 
            // Currency conversion type: OneToMany   
            // Selected members to be converted: Sales Amount    
            // Please be aware that any changes that you decide to make to it may be overridden the next time you run the Currency Conversion wizard again. 

            // All currency conversion formulas are calculated for the non pivot currency and at leaf of the time dimension
            Scope ( { Measures.[Sales Amount]} );
                  Scope( Leaves([Date]) ,
                        Except([Reporting Currency].[Currency].[Currency].Members, [Reporting Currency].[Currency].[Currency].[US Dollar]));

                // This section overrides the Pivot Currency values with the Converted value for each selected measures/account members/account type members needing to be converted with Measure rate End Of Day Rate
                // LinkMember is used to reference the currency from the source currency dimension in the rate cube. 
                Scope( { Measures.[Sales Amount]} );
                       This = [Reporting Currency].[Currency].[US Dollar] / (Measures.[End Of Day Rate],LinkMember([Reporting Currency].[Currency].CurrentMember, [Currency].[Currency])) ;
                End Scope;   

                  End Scope; // Leaves of time and non pivot currency  
            End Scope; //
         // End of the currency conversion wizard generated script
      // </Currency conversion>

Having done some (thorough) scoping, the line of code that is of real interest is

This = [Reporting Currency].[Currency].[US Dollar] / (Measures.[End Of Day Rate], LinkMember([Reporting Currency].[Currency].CurrentMember, [Currency].[Currency])) ;

This line is doing the division in a similar way to the measure expression.  However, it is using the LinkMember function to do a runtime link between Currency and Reporting Currency based on the member name.  This is effectively just replacing the Currency dimension with the Reporting Currency dimension for reporting purposes.

SUMMARY

As I said, if all you have is a one-to-many currency conversion requirement, I think the measure expressions approach is more elegant.  It is optimized to perform a little better for large volumes (despite the fact that cell assignments in MDX script do perform very well).  Also, there is only one currency dimension (instead of Currency and Reporting Currency).  For many cubes that just require one-to-many currency conversion, this is a simpler model.  Plus a lot of the work you have to do to use the measure expressions approach has to be done anyway in order to run the wizard.  This is in contrast to the Time Analysis wizard, which I think adds great value.  I think the currency conversion wizard adds more value for many-to-many currency conversion.  In this case, you would invariably want the 2 currency dimensions.