SSAS Tabular Case Study: Change Cases of Member

Happy New Year, and I hope everyone all the best to 2019.

To begin with 2019, I just want to keep it simple. Let's see a small problem I found in SSAS tabular model recently: It could be very confusing when we need to change the case of a dimension member. To demonstrate this problem, I created a very simple table:

use master
go

create database demo
go

use demo
go

if exists (select 1 from sys.tables where name = 'product')
drop table product
go

create table PRODUCT
(
ProductKey int identity(1,1),
ProductName varchar(100),
Country varchar(100),
Constraint PK_Product Primary Key (ProductKey)
)
go

Insert into Product(ProductName, Country)
values
('BIKE', 'AU'),
('BIKE', 'UK'),
('BIKE', 'US'),
('BIKE', 'CN'),
('BIKE', 'NZ')
go

Select * from PRODUCT

As you can see it is a very simple table

And then I just created a tabular model based on this simple table.

Now I bring in the Power BI to visualise the product name by country

So far everything is good. But assume CN's manager wants to make some formatting changes: I want to see the product name in proper case, "Bike". Looks very simple, doesn't it? So we go to the database, update product name to proper case, re-process the model.

Update PRODUCT set ProductName = 'Bike' where Country = 'CN' 

Now refresh the report, nothing happens!

What happened? You can find the reason here. In short, it is because tabular model uses columnar storage mode, so for the same string value, it will only save the first instance regardless of its form. So in tabular model, "BIKE", "bike", "Bike", and "biKE" are all saved as one entry "BIKE". So now doesn't mater how many times you update your data, or re-process your tabular model, the report will always show "BIKE" as the product name.

To approve this, let's update all "BIKE" to "Bike" now, then re-process the model.

Now we go back to the PBI report and refresh it.

So here is the conclusion. When we design the data model, usually we will denormalise the data to build the dimension, which obviously, will generate lots of duplicated entries. When there is a requirement to show members in different forms, we need to be very careful due to the storage mode. In my practice, I would suggest to have a format string field build into the model.

Power BI: Parameter Table and Dynamic Date Range

As we know Power BI is all about filter context, which brings us quite a lot convenience. But for some cases, it might not be what we want. For instance, what if I want to show amount of sales for last N months by a calendar month slicer? Because the slicer puts a constraint on the calendar month, you won't be able to show sales amount month by month. As illustrated below, how can we see total sales for 2012-12, 2012-11, etc.?

Normally what we can do is creating a disconnected date dimension, and using this disconnected date dimension for the slicer. But sometimes it could be very confusing to end users. So instead of creating a second date dimension, we can try to use date member from the fact table directly. So let's start today's topic:

Firstly the demonstration I built is from AdventureWorksDW2014.

To make it simple, I only loaded DimDate and FactInternetSales tables. Then, I removed default relationships and create the date relationship between FullDateAlternateKey and OrderDate columns directly.

Finally I created a custom column in the DimDate table for calendar month:

Calendar Month = FORMAT(DimDate[FullDateAlternateKey], "yyyy-MM")

Then we need a parameter table, which gives us control of how many months of sales user wants to see. In the real world scenario, we may need to create a junk dimension to hold these values. But in this demonstration, I just manually input the data.

Now let's create two simple measures:

Total Sales = SUM(FactInternetSales[SalesAmount])

Selected Period = IF(HASONEVALUE('Parameter Table'[Parameter Value]), VALUES('Parameter Table'[Parameter Value]))


If we follow normal steps to create visuals (create a slicer from 'DimDate'[Calendar Month], create a slicer from 'Parameter Table'[Parameter Name], then drop the table visual for calendar month and total sales), you will find the table visual won't show last N months sales, because the calendar month slicer puts a filter context to the fact table.

So let's make three changes to make it work:

1. add a calculate order month column into the fact table

Order Month = FORMAT(FactInternetSales[OrderDate], "yyyy-MM" )

2. add a measure for total sales of last N months. (note: Max function gives us the last date within the current filter context - the calendar month slicer value in this case)

Sales (in period) = CALCULATE([Total Sales], DATESINPERIOD(DimDate[FullDateAlternateKey], MAX(DimDate[FullDateAlternateKey]), -[Selected Period], MONTH))

3. now instead of using 'DimDate'[Calendar Month] and [Total Sales] for the table, we drag and drop 'FactInternetSales'[Order Month] and [Sales (in period)] into the table visual.

Easy job, isn't it? :D

Pull Exchange email attachment via Exchange Service Binding

To be honest, I had never thought there would be a need to pull attachment from on-premise Exchange Server via Exchange Service Binding, in the year of 2018. But I was wrong :(

Basically we have two ways to read email from on-premise Exchange. The first option is utilizing Exchange Web Service managed API. It can be used for both on-premise Exchange server and O365 (an example I blogged before). The other option is today's topic: Exchange Service Binding, which by any means, should be avoid! Now let's have a look the basic routine:

• find the root of the email account;
• locate the base email folder (normally it should be the inbox folder);
• pull emails from the base folder;
• get email attachments' id from the email item;
• pull attachments from email by attachment item id;

To locate the root folder of the account (NOT the inbox folder)

        public static string GetRootFolder(ExchangeServiceBinding esb)
        {
            DistinguishedFolderIdType[] dfit = new DistinguishedFolderIdType[1];

            dfit[0] = new DistinguishedFolderIdType();
            dfit[0].Id = DistinguishedFolderIdNameType.root;

            FolderResponseShapeType frst = new FolderResponseShapeType();
            frst.BaseShape = DefaultShapeNamesType.AllProperties;

            GetFolderType gftRoot = new GetFolderType();
            gftRoot.FolderIds = dfit;

            gftRoot.FolderShape = frst;
            GetFolderResponseType gfrt = esb.GetFolder(gftRoot);
            FolderInfoResponseMessageType firmt = ((FolderInfoResponseMessageType)gfrt.ResponseMessages.Items[0]);

            if (firmt.ResponseClass == ResponseClassType.Success)
                return ((FolderInfoResponseMessageType)gfrt.ResponseMessages.Items[0]).Folders[0].FolderId.Id;
            else
                return null;

        }

After we have the root, we can navigate to the specific folder by folder name, inbox, archive, deleted folder, whatever you like :)

        public static BaseFolderType GetFolder(ExchangeServiceBinding esb, string parentFolderId, string folderName)
        {
            if (esb == null || string.IsNullOrEmpty(parentFolderId))
                return null;

            FolderIdType[] fit = new FolderIdType[1];
            fit[0] = new FolderIdType();
            fit[0].Id = parentFolderId;

            FolderResponseShapeType frst = new FolderResponseShapeType();
            frst.BaseShape = DefaultShapeNamesType.AllProperties;

            PathToUnindexedFieldType ftFolderName = new PathToUnindexedFieldType();
            ftFolderName.FieldURI = UnindexedFieldURIType.folderDisplayName;

            ConstantValueType cvt = new ConstantValueType();
            cvt.Value = folderName;

            FieldURIOrConstantType ctFolderName = new FieldURIOrConstantType();
            ctFolderName.Item = cvt;

            ContainsExpressionType cet = new ContainsExpressionType();
            cet.Constant = cvt;
            cet.Item = ftFolderName;
            cet.ContainmentComparison = ContainmentComparisonType.IgnoreCase;
            cet.ContainmentComparisonSpecified = true;
            cet.ContainmentMode = ContainmentModeType.FullString;
            cet.ContainmentModeSpecified = true;
            RestrictionType rt = new RestrictionType();
            rt.Item = cet;
             
            FindFolderType fft = new FindFolderType();
            fft.Traversal = FolderQueryTraversalType.Deep;
            fft.ParentFolderIds = fit;
            fft.FolderShape = frst;
            fft.Restriction = rt;

            FindFolderResponseType ffrt = esb.FindFolder(fft);

            ResponseMessageType rmt = ((ResponseMessageType)ffrt.ResponseMessages.Items[0]);

            if (rmt.ResponseClass == ResponseClassType.Success)
            {
                BaseFolderType[] bfts = ((FindFolderResponseMessageType)ffrt.ResponseMessages.Items[0]).RootFolder.Folders;
                if (bfts.GetLength(0) > 0)
                    return bfts[0];
                else
                    return null;
            }
            else
                return null;
        }

Now we can get all email items from the folder we located

        public static List GetEmailItems(ExchangeServiceBinding esb)
        {
            List lstItems = new List();

            FindItemType fitRequest = new FindItemType();
            fitRequest.Traversal = ItemQueryTraversalType.Shallow;
            ItemResponseShapeType irst = new ItemResponseShapeType();
            irst.BaseShape = DefaultShapeNamesType.AllProperties;
            fitRequest.ItemShape = irst;

            DistinguishedFolderIdType[] dfits = new DistinguishedFolderIdType[1];
            dfits[0] = new DistinguishedFolderIdType();
            dfits[0].Id = DistinguishedFolderIdNameType.inbox;
            fitRequest.ParentFolderIds = dfits;

            FindItemResponseType fitResponse = esb.FindItem(fitRequest);
            FindItemResponseMessageType findItemResponseMessageType = (FindItemResponseMessageType)fitResponse.ResponseMessages.Items[0];

            var responseContents = findItemResponseMessageType.RootFolder.Item;
            var items = ((ArrayOfRealItemsType)responseContents).Items;

            if (items != null && items.Length > 0)
            {
                foreach (var item in items)
                {
                    lstItems.Add(item);
                }
            }

            return lstItems;
        }

Now here is a very important concept: if you google Exchange Service Binding, you will find the concept of the attachment in ESB is a bid confusing: the Attachments property returned from email ItemType is not the actual attachment itself. So we need to get the item id of the attachment, then we can get the actual attachment from the item id. Below is the code to construct a dictionary object for attachment id and attachment name:

        public static Dictionary GetAttachments(ExchangeServiceBinding esb, ItemIdType itemId)
        {
            Dictionary attachments = new Dictionary();

            GetItemType gitRequest = new GetItemType();
            gitRequest.ItemIds = new ItemIdType[] { itemId };
            gitRequest.ItemShape = new ItemResponseShapeType();
            gitRequest.ItemShape.BaseShape = DefaultShapeNamesType.AllProperties;

            PathToUnindexedFieldType hasAttachPath = new PathToUnindexedFieldType();
            hasAttachPath.FieldURI = UnindexedFieldURIType.itemHasAttachments;

            PathToUnindexedFieldType attachmentsPath = new PathToUnindexedFieldType();
            attachmentsPath.FieldURI = UnindexedFieldURIType.itemAttachments;

            gitRequest.ItemShape.AdditionalProperties = new BasePathToElementType[] { hasAttachPath, attachmentsPath };

            GetItemResponseType giResponse = esb.GetItem(gitRequest);
            ItemInfoResponseMessageType iirm = (ItemInfoResponseMessageType)giResponse.ResponseMessages.Items[0];

            if (iirm.ResponseCode == ResponseCodeType.NoError)
            {
                ItemType it = iirm.Items.Items[0];

                if (it.HasAttachments && it.Attachments != null && it.Attachments.Length > 0)
                {
                    foreach (AttachmentType attachmentItem in it.Attachments)
                    {
                        FileAttachmentType fat = (FileAttachmentType)attachmentItem;
                        if (fat != null)
                        {
                            attachments.Add(fat.AttachmentId.Id, fat.Name);
                        }
                    }

                    
                }
            }

            return attachments;
        }

Then we can get the attachment and save to the disk

        public static void GetAttachmentsOnItem(ExchangeServiceBinding esb, ItemIdType itemId, string destPath)
        {
            GetItemType gitRequest = new GetItemType();
            gitRequest.ItemIds = new ItemIdType[] { itemId };
            gitRequest.ItemShape = new ItemResponseShapeType();

            gitRequest.ItemShape.BaseShape = DefaultShapeNamesType.IdOnly;

            PathToUnindexedFieldType hasAttachPath = new PathToUnindexedFieldType();
            hasAttachPath.FieldURI = UnindexedFieldURIType.itemHasAttachments;

            PathToUnindexedFieldType attachmentsPath = new PathToUnindexedFieldType();
            attachmentsPath.FieldURI = UnindexedFieldURIType.itemAttachments;
            gitRequest.ItemShape.AdditionalProperties = new BasePathToElementType[] { hasAttachPath, attachmentsPath };

            GetItemResponseType getItemResponse = esb.GetItem(gitRequest);

            ItemInfoResponseMessageType getItemResponseMessage = (ItemInfoResponseMessageType)getItemResponse.ResponseMessages.Items[0];

            if (getItemResponseMessage.ResponseCode == ResponseCodeType.NoError)
            {
                ItemType item = getItemResponseMessage.Items.Items[0];

                if (item.HasAttachments && (item.Attachments != null) && (item.Attachments.Length > 0))
                {
                    List lstRait = new List();

                    for (int attachmentIndex = 0; attachmentIndex < item.Attachments.Length; attachmentIndex++)
                    {
                        FileAttachmentType fat = (FileAttachmentType)item.Attachments[attachmentIndex];
                        if (fat != null)
                        {
                            RequestAttachmentIdType rait = new RequestAttachmentIdType();
                            rait.Id = fat.AttachmentId.Id;
                            lstRait.Add(rait);
                        }
                    }

                    GetAttachmentType gat = new GetAttachmentType();
                    gat.AttachmentShape = new AttachmentResponseShapeType();
                    gat.AttachmentIds = lstRait.ToArray();

                    GetAttachmentResponseType getAttachmentResponse = esb.GetAttachment(gat);

                    foreach (AttachmentInfoResponseMessageType attachmentResponseMessage in getAttachmentResponse.ResponseMessages.Items)
                    {
                        if (attachmentResponseMessage.ResponseCode == ResponseCodeType.NoError)
                        {
                            FileAttachmentType fileAttachment = attachmentResponseMessage.Attachments[0] as FileAttachmentType;
                                                    
                            using (FileStream file = File.Create(Path.Combine(destPath, fileAttachment.Name)))
                            {

                                file.Write(fileAttachment.Content, 0, fileAttachment.Content.Length);
                                file.Flush();
                                file.Close();
                            }

                        }

                    }

                }

            }

        }