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 normally 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();
        }

    }
}
About these ads

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

%d bloggers like this: