How to use SUMMARIZECOLUMNS Function (DAX) in Power BI and SSAS Tabular

SUMMARIZECOLUMNS function returns a summary table over a set of groups. This new function is included in SQL Server 2016 Analysis Services (SSAS), Power Pivot in Excel 2016, and Power BI Desktop. SUMMARIZECOLUMNS, which is a replacement of SUMMARIZE and does not require the use of ADDCOLUMNS to obtain good performance

Power BI

SUMMARIZECOLUMNS function is very useful in Power BI report, especially to produce summarize/group/aggregate tables.

For example, if you need to create a summary table for sales value by subcategory, a year with some filters.

Summary Product Subcategory = SUMMARIZECOLUMNS

(

'Product Category'[Category],

'Product Subcategory'[Subcategory],

'Date'[Calendar Year],

FILTER('Product Category','Product Category'[Category]="Audio"),

"Total Sales",'Sales'[Sales Amount]

)

SSAS Tabular

You can use the function in SSAS cubes in similar ways, but you need to be aware how to use the syntaxes.

The following examples will give a clear idea about how to use SUMMARIZECOLUMNS function.

EVALUATE

SUMMARIZECOLUMNS

(

'Date'[Calendar Year],

"Total Sales",'Internet Sales'[Internet Total Sales]

)

EVALUATE

SUMMARIZECOLUMNS

(

'Product Category'[Product Category Name],

'Date'[Calendar Year],

"Total Sales",'Internet Sales'[Internet Total Sales]

)

EVALUATE

SUMMARIZECOLUMNS

(

'Product Category'[Product Category Name],

'Date'[Calendar Year],

FILTER('Date','Date'[Calendar Year]=2014),

"Total Sales",'Internet Sales'[Internet Total Sales],

"Total Units",'Internet Sales'[Internet Total Units]

)

EVALUATE

SUMMARIZECOLUMNS

(

'Product Category'[Product Category Name],

'Date'[Calendar Year],

FILTER('Date','Date'[Calendar Year]=2014),

"Total Sales",'Internet Sales'[Internet Total Sales],

"Total Sales with Correction",'Internet Sales'[Internet Total Sales]*0.99,

"Total Units",'Internet Sales'[Internet Total Units]

)

EVALUATE

SUMMARIZECOLUMNS

(

'Product Subcategory'[Product Subcategory Name],

'Date'[Calendar Year],

FILTER('Product Category','Product Category'[Product Category Name]="Bikes"),

"Total Sales",'Internet Sales'[Internet Total Sales],

"Total Units",'Internet Sales'[Internet Total Units]

)

Cheers! Uma

How to handle Many to Many relationships in data warehouse dimensional modeling

In a relational database, the many-to-many relationship between two tables is resolved through third intermediate table. This intermediate table sometimes called as bridge table.

For example, relationship between FactInternetSales and DimSalesReason though the FactInternetSalesReason intermediate table.

For a data warehouse in a relational database management system, this is the correct model and you can write your own queries as you want, however, SSAS is not supported many-to-many in this similar model.

To solve this problem by creating intermediate dimension between both fact tables. You create it from the primary key of the FactinternetSales table. Let’s call this dimension DimFactinternetSales. The relationship between the FactInternetSales and the new DimFactInternetSales dimension is one-one.

The following images show how to implement in actual scenario using SSAS multidimensional model

Cheers! Uma

How to trigger tweets to email using Logic Apps

Logic Apps one of the coolest product from Azure. It makes building integration solutions is easier than ever. Logic Apps brings speed and scalability into the enterprise integration space. The ease of use of the designer, variety of available triggers and actions, and powerful management tools make centralizing your APIs simpler than ever.

This post covers how to design and run a small logic app that trigger new tweets to your email.

Create a Blank Logic App

This will open Logic App Designer and choose Twitter service and provide the twitter information that you need to email as shown below.

In this example, tweet is getting from #logicapp and frequency set to 1 Minute interval. Next Choose an action to send email whenever new tweet tag with #logicapp.

You can choose one of the email services to send email. In here Office 365 email is used and configure the required fields as shown below.

That’s all, just save and close the designer, this will direct you to admin portal.

To run the app, click on Run Trigger.

Cool, now time to test! once you tweet, you will receive an email from logic app as shown below.

You can monitor the trigger easily via Azure Portal, this gives more flexibility to manage and monitoring the Logic Apps.

Cheers! Uma

How to setting up Tabular model in Azure Virtual Machine

On the Azure portal, click New. The portal opens the New blade. The SQL Server VM resources are in the Compute group of the Marketplace.

After selecting the image, follow the next steps setting up the VM. Tabular instance is not set up by default. The SQL Server installation media is on the VM, so you can run that to install your tabular instance. 

Cheers! Uma

How to generate Smart date key from Year, Month and Date

SELECT CONVERT (INT, CONVERT(VARCHAR (8), DATEFROMPARTS([year],[month],[date]),112))

FROM dbo.FactSales;

DATEFROMPARTS (year, month, day)

112 Format = yyyymmdd ISO

Azure Storage step by step guide

Azure Storage is the cloud storage solution for modern applications that rely on durability, availability, and scalability to meet the needs of their customers. You can easily create and manage your storage for low cost via Azure Management Portal.

There are two types of storage accounts:

General-purpose Storage Accounts: A general-purpose storage account gives you access to Azure Storage services such as Tables, Queues, Files, Blobs and Azure virtual machine disks under a single account.

Blob Storage Accounts: A Blob storage account is a specialized storage account for storing your unstructured data as blobs (objects) in Azure Storage.

Blob storage

For users with large amounts of unstructured object data to store in the cloud, Blob storage offers a cost-effective and scalable solution.

Blog = Binary Large Object

You can use Blob storage to store content such as:

• Documents
• Virtual Machines
• Social data such as photos, videos, music, and blogs
• Backups of files, computers, databases, and devices
• Images and text for web applications
• Configuration data for cloud applications
• Big data, such as logs and other large datasets

Table storage

Modern applications often demand data stores with greater scalability and flexibility than previous generations of software required. For example, the following table for cloud-based gaming table to store user details.

Queue storage

Messages are inserted into queues and kept in order until they are delivered.  Individual messages may be up to 64 KB in size, but queue can hold 200 TB of messages.

File storage

Azure File storage offers cloud-based SMB file shares so that you can migrate legacy applications that rely on file shares to Azure quickly and without costly rewrites.

The following steps show how to create a file storage

Step1: Create storage account

Step 2: Create file share

Step3: Add few files and create a directory

Step 4: Get the URL by click on Properties and you can use as file server

https://umastorage.file.core.windows.net/umafiles

You also can mount this file storage into your Windows or Linux OS. You can get required command by click on connect.

For Windows

> net use [drive letter] \\umastorage.file.core.windows.net\umafiles /u:AZURE\umastorage 0GDgfkzL/NYFubiSkHb5y40vF3aIMRKrb9sOPgQnjcyK20lOMcjRmFrXmLv2t+X11A+7300K4pv61GRt/OYffg==

For Linux

> sudo mount -t cifs //umastorage.file.core.windows.net/umafiles [mount point] -o vers=3.0,username=umastorage,password=0GDgfkzL/NYFubiSkHb5y40vF3aIMRKrb9sOPgQnjcyK20lOMcjRmFrXmLv2t+X11A+7300K4pv61GRt/OYffg==,dir_mode=0777,file_mode=0777

Once you mount the file storage you can use this file storage same as other drive through the explorer.

For disconnect, you can right click and disconnect

In a similar way, you can create blog storage as well

https://umastorage.blob.core.windows.net/umadrive

You can use this drive to attach your virtual machine

Cheers!
Uma

How to use merge instead of slowly changing dimension

Performs insert, update, or delete operations on a target table based on the results of a join with a source table.

MERGE INTO dbo.productdata AS target

USING dbo.staging_productdata AS source

   ON target.ProductNumber = source.ProductNumber

   AND target.DateTime = source.DateTime

WHEN MATCHED THEN

   UPDATE SET target.product = source.product

WHEN NOT MATCHED BY TARGET THEN

   INSERT (ProductNumber, DateTime, product)

   VALUES (source.ProductNumber, source.DateTime, source.product)

--With delete

MERGE INTO dbo.productdata AS target

USING dbo.staging_productdata AS source

   ON target.ProductNumber = source.ProductNumber

   AND target.DateTime = source.DateTime

WHEN MATCHED THEN

   UPDATE SET target.product = source.product

WHEN NOT MATCHED BY TARGET THEN

   INSERT (ProductNumber, DateTime, product)

   VALUES (source.ProductNumber, source.DateTime, source.product)

WHEN NOT MATCHED BY SOURCE THEN

   DELETE

Cheers!
Uma