Tag Archives: Power Pivot

Creating Custom Table in SSAS Tabular using Table and Row Constructors in DAX

Dynamic_Measures_in_Card_Visual_-_Power_BI_Desktop

A while ago I was working on a Power BI project which the customer wanted to define a new table directly in the model. The easiest way to achieve this in Power BI Desktop is to “Enter Data” which creates a new table by typing or pasting in new contents. I was thinking of that project the other day and thought, hey, how we can do the same in SSAS Tabular when there is no Power Query (M) language available in SSAS Tabular 2016. The good news is that Power Query will be available in the next version of SSAS Tabular in SQL Server vNext. But, until then a workaround would be entering data to a CSV file then load it to the model. Another way is to implement custom tables in DAX in SQL Server 2016 Tabular models using Table and Row Constructors. In this post I show you a way of creating custom table in SSAS Tabular using table constructors in DAX. You can do the same in Power BI as the same principle applies. Therefore, in case you’d prefer not to use “Enter Data” feature which effectively uses Power Query to create a new table in Power BI Desktop, then you can use DAX to do the same.

Requirements

If don’t already have SQL Server 2016 it’s probably time to download and install it. I use AdventureWorksDW as sample database in this article.

Scenario

You are involved with an SSAS Tabular project and the customer asked for a report in Power BI with dynamic Card so that the values shown in the Card visual should dynamically change based on selected measure from a slicer. You have several different measures in the model and the customer wants to show some of them dynamically in only one Card visual. Consider you have the following measures to be shown in the Card:

  • Total Internet Sales
  • Internet Sales in 2014
  • Total Number of Internet Sales Transactions

You have to create a logic so that the users can selected any of the above measures to show in a single Card visual.

How it works

After you meet the requirements, you’re good to start implementing the above scenario in SQL Server Data Tool (SSDT). Creating a calculated table in SSAS Tabular 2016 is fairly easy. All we need to do is to create a custom table with two columns. One column stores friendly names for measures and the other one holds DAX expressions for the measures. As you might have noticed, I’m talking about creating a custom table in DAX and populating it with values. Continue reading to see how. What we are going to do is to create a calculated table using table constructors in DAX. Table and Row Constructors weren’t available in previous versions of DAX in SSAS Tabular. They are very similar to Lists or a list of Tuples just like what we have in MDX.

I’ll explain this later when we created our sample model in SSDT. Continue reading Creating Custom Table in SSAS Tabular using Table and Row Constructors in DAX

Use KPI in Table, Matrix and Card Visualisations in Power BI

Use KPI in Table, Matrix and Card Visualisations in Power BI

One of the coolest features in Power Pivot is the ability to define KPIs based on calculated measures. You can create KPIs in SSAS Tabular as well. Unfortunately, this feature is missing from Power BI. In this post I show you a very simple way to import KPIs and use them in Table, Matrix, Multi-row card and Card visualisations in Power BI.

I use the word “IMPORT” as this feature is NOT available in Power BI Desktop yet so we CANNOT create KPIs directly in Power BI Desktop, but, there is work around for it that I explain it in this post.

Requirements

  • Latest version of Power BI Desktop
  • Microsoft Excel (2007 or later)
  • Power Pivot add-on if using Excel 2007 to 2013 (Power Pivot is already available in Excel 2016)
  • Power Query add-on if you need to transform your data (Power Query is available only in Excel 2010 Professional Plus and Excel 2013. It’s added to Excel 2016 as a built-in feature. Check this out to find out more about BI features in Excel 2016.). In this post I’m not loading data using Power Query, so you can ignore Power Query if you want to follow this article to make your first sample KPI work.

How It Works

The work around is really easy. You only need to

  • open Excel
  • load data into Power Pivot model from your source
  • create desired calculated measures in Power Pivot
  • create desired KPIs on top of your calculated measure(s)
  • save the Model (Excel file)
  • import the Model to Power BI Desktop

Let’s go through the whole process step-by-step to see how it works on real world.

Note: I use Excel 2016 and Adventure Works DW SQL Server sample database. If you’re using prior versions of Excel, you have to download and install Power Pivot for Excel. All steps below are pretty much the same.

  • Open Excel 2016
  • From Data tab click “Manage Data Model”

Excel 2016 Manage Data Model

Note: In case you’re using prior versions of Excel you need to click “Manage” from Power Pivot tab. All other steps would be the same.

  • Get external data from SQL ServerPower Pivot Get External Data From
  • Enter server name and database name then click Next

Power Pivot Get Data From SQL Server

  • Select “FactResellerSales”, “DimProduct”, “DimProductCategory”  and “DimProductSubCategory” then click Finish

Power Pivot Get Data From SQL Server

  • After the data successfully imported click Close

Power Pivot Get Data From SQL Server

Continue reading Use KPI in Table, Matrix and Card Visualisations in Power BI

Three different approaches for creating a BISM in SSAS 2012, Tabular vs. Multidimensional vs. PowerPivot

SSAS 2012 supports three different approaches for creating a BISM (Business Intelligence Semantic Model):

1. Tabular

a. Uses relational modelling constructs like such as tables and relationships

b. Uses xVelocity in-memory analytics engine for sorting and data calculations

c. Needs to use SSDT (SQL Server Data Tools) to implement

d. Can import data from relational data sources using OLE DB native and managed providers

e. Tabular solutions only support one model.bim file per solution, which means that all work must be done in a single file. Development teams that are accustomed to working with multiple projects in a single solution might need to revise how they work when building a shared tabular solution.

f. support DAX calculations, DAX queries, and MDX queries

g. Tabular model databases can use row-level security, using role-based permissions in Analysis Services (DAX implementation required)

h. It might be not a good choice for the systems that are going to load terabytes of data

Continue reading Three different approaches for creating a BISM in SSAS 2012, Tabular vs. Multidimensional vs. PowerPivot