Posts

New Tabular Schema in SQL Server 2016

/0 Comments/in /by

Those of you who had to generate programmatically Tabular models or make changes to the schema would agree that it was more complicated that it should have been. That’s because Tabular borrowed the schema from Multidimensional so objects had to be defined in different places and in the SSAS MD terminology (dimensions, DSVs, etc). SQL Server 2016 CTP3 brings a new and much more simplified Json-based schema for describing SSAS Tabular models. To see it, you need to configure your workspace database in SQL Server 2016 (1200) compatibility mode. SSMS doesn’t yet support the new schema but you can take a look at Model.bim once you create your project. When you open the source of the Model.bim file you’ll see something completely different than the XML schema you were used to:

{

“name”: “SemanticModel”,

“id”: “SemanticModel”,

“compatibilityLevel”: 1200,

“readWriteMode”: “readWrite”,

“model”: {

“culture”: “en-US”,

“dataSources”: [

{

“name”: “DW”,

“connectionString”: “Provider=SQLNCLI11;Data Source=.;Initial Catalog=AdventureworksDW2016CTP3;Integrated Security=SSPI;Persist Security Info=false”,

“impersonationMode”: “impersonateServiceAccount”,

“annotations”: [

{

“name”: “ConnectionEditUISource”,

“value”: “SqlServer”

},

{

“name”: “PasswordRemoved”

}

]

}

],

“tables”: …

So faster Tabular schema operations in SQL 2016 and much easier model auto-generation. Now all we need is a programmatic way to work with the new schema and this should probably come soon.

Implementing Conditional Formatting in Tabular

/0 Comments/in /by

As I mentioned here and here, Marco and I teamed up to invigorate the DAX Editor with some new features. Today we officially released the latest updates and published in to the Visual Studio Gallery. The easiest way to install is to do it directly from Visual Studio/SSDT:

  1. In Visual Studio 2010, 2012, or 2013, go to Tools->Extensions and Updates.
  2. Click the Online tab and search for “dax” or “dax editor”. Note that if you use Visual Studio 2013, you need to make a configuration change in devenv.exe.config that I explained here.

040715_0137_Implementin1

In the second post, I’ve mentioned that I’ve added the option to inject some custom MDX script in the Tabular script, such as to set up default members. You can also use the custom script to implement conditional formatting – a feature that Tabular doesn’t support natively. Suppose that you want to change the front color of the measure Products with Negative Stock to Red when it’s above zero.

040715_0137_Implementin2

The following scope assignment in DAX Editor gets the job done:

 

— MDX SCRIPT —

       SCOPE( Measures.AllMembers );

              IF Measures.CurrentMember IS [Measures].[Products with Negative Stock] AND [Measures].[Products with Negative Stock] > 0 Then

                     FORE_COLOR(THIS) = 255

              End If; 

       END SCOPE;

— MDX SCRIPT ––

 

The more convoluted syntax in this case is because [Products with Negative Stock] is a calculated member. If it was a regular measure, the scope assignment could be simplified:

 

— MDX SCRIPT —

       SCOPE({[Measures].[Products with Negative Stock]});

              FORE_COLOR(THIS) = Iif (Measures.CurrentMember > 0, 255, 0);

       END SCOPE;

— MDX SCRIPT ––

 

Yet, another way to do it is to define the measure using MDX thanks to the fact that everything defined between the “–MDX SCRIPT –” placeholders is carried verbatim to the cube script. We can say that that’s another DAX Editor feature especially if you need MDX features that are not supported in DAX, such as hierarchy navigation (ANCESTOR, DESCENDANTS, etc.) or KPIs.

— MDX SCRIPT —

    CREATE MEMBER CURRENTCUBE.[Measures].[Products with Negative Stock 1] AS <some calculation>, FORE_COLOR = IIF([Measures].[Products with Negative Stock 1] > 0, 255 /*Red*/, 0 /*Black*/);

— MDX SCRIPT –-

You got the idea. You can stuff in any valid MDX statement that Multidimensional supports in the cube script.

DAX Editor Adds Support for Tabular Default Members

/0 Comments/in /by

UPDATE 4/24/2019   The new JSON-based Tabular schema doesn’t support extensions so Tabular Editor and BI Developer Extensions (BIDS) won’t work.

In my previous blog, I announced a few new features for DAX Editor for Tabular. Today, I checked in another change that adds support for default members. Currently, Tabular doesn’t have UI for defining default members. However, you can define default members in the MdxScript section of the BIM file using MDX syntax just like you can do so in Multidimensional.

031915_0156_DAXEditorAd1

The only issue is that if you make a change to any Tabular calculated measure in the designer, SSDT will regenerate the script and your manual changes will be lost. That is unless you use DAX Editor as its support for default members keeps them in the script. As a disclaimer, I took the backdoor approach for defining the default member syntax. The right approach would be to build upon the wonderful work of Nickolai Medveditskov and check the default member syntax so DAX Editor catches syntax errors as you type and when you attempt to save the measures. But that would have required a lot of work with Managed Babel. Moreover, the next version of SQL Server might support default members natively so all this work could be wasted.

So, I’ve decided to implement default members by allowing you to define a custom MDX script section in the *.dax file that is carried verbatim to the Tabular model without any syntax checking or changes. Of course, if you make an error, e.g. reference a column that doesn’t exist or use wrong syntax, you’ll find this pretty quickly when you attempt to open the *.bim file. To fix this, check your syntax in DAX Editor and save the changes to the *.bim file again. Or, open the *.bim file source and remove the custom MDX script which you’ll find inside the <MdxScript> element.

The syntax for default members is simple:

  1. In the DAX file, add the following section at the top of the file. It actually can appear anywhere but I’d typically put it on top when working with cube scripts.

    — MDX SCRIPT —

    ALTER Cube CurrentCube Update Dimension [Product].[Is Finished Goods], Default_Member = [Product].[Is Finished Goods].&[TRUE];

    ALTER Cube CurrentCube Update Dimension [Currency].[Currency Code], Default_Member = [Currency].[Currency Code].&[USD];

    — MDX SCRIPT —

IMPORTANT Notice that the script block has to be enclosed with “— MDX SCRIPT –” comments exactly as they appear in the example. DAX Editor looks for this pair of comments to identify the custom script block.

  1. Inside the comments, enter ALTER CUBE statements to set the default members using MDX syntax as you would do it in Multidimensional cube scripts. Notice the MDX UniqueMember syntax for the Default_Member property. In this case, I set two default members. Specifically, the Is Finished Goods column in the Product table is set to the TRUE member () and the Currency Code column in the Currency table is set to USD.
  2. Click the “Save Measures to BIM File” button in the DAX Editor Toolbar to apply the DAX Editor changes to the *.bim file as usual.

031915_0156_DAXEditorAd2

I haven’t tested it yet but you should be able to add other MDX script commands to the DAX Editor script, such as KPI definitions, which DAX Editor doesn’t currently support.

Enjoy!

DAX Editor New Features

/0 Comments/in /by

UPDATE 4/24/2019   The new JSON-based Tabular schema doesn’t support extensions so Tabular Editor and BI Developer Extensions (BIDS) won’t work.

As it stands, Tabular (versions 2012 and 2014) doesn’t have the equivalent of a cube script. Instead, the developer has to use the Measures grid to maintain DAX calculated measures. This is OK with a few measures but as the number of measures grows, the development experience suffers because it’s getting harder to locate these measures. Besides, every time you make a change, you need to wait for the Tabular environment to refresh which gets annoying quickly. However, you can use the DAX Editor community sample to simulate a Tabular script. DAX Editor allows you to extract all measures from a Tabular model and maintain them outside the model in a DAX file. DAX Editor was initially developed by Microsoft (kudos to Nickolai Medveditskov).

Marco Russo and I teamed up to add some new features to DAX Editor as follows:

  1. Support of Visible and Description properties. Although only Power View supports field descriptions (Excel doesn’t), entering user-friendly descriptions allows you to implement self-documented models. For example, you can get a list of all measures and descriptions from the $SYSTEM.MDSCHEMA_MEASURES DMVs. The following example shows you how to use Visible and Description properties:

    CREATE MEASURE ‘Internet Sales'[Internet Distinct Count Sales Order]=DISTINCTCOUNT([Sales Order Number]) CALCULATION PROPERTY NumberWhole Visible=False ThousandSeparator=True Format=’#,0′ Description=’Returns unique number of sales orders’;

 Notice that even if don’t want to overwrite the measure format, you still have to specify the default General format so that DAX Editor can parse the syntax:

CREATE MEASURE ‘Internet Sales'[Internet Distinct Count Sales Order]=DISTINCTCOUNT([Sales Order Number]) CALCULATION PROPERTY General Visible=False Description=’Returns unique number of sales orders’;  

     2.   Support of Visual Studio 2013 although it requires a workaround to fix the bindings for the Microsoft.VisualStudio.Package.LanguageService.* dlls. Specifically, you need to modify the VS 2013 devenv.config (C:\Program Files (x86)\Microsoft Visual Studio 12.0\Common7\IDE\devenv.exe.config) as follows:

<dependentAssembly>

       <assemblyIdentity name=“Microsoft.VisualStudio.Package.LanguageService.11.0“ publicKeyToken=“b03f5f7f11d50a3a“ culture=“neutral“/>

       <bindingRedirect oldVersion=“2.0.0.0-11.0.0.0“ newVersion=“11.0.0.0“/>

</dependentAssembly>

<dependentAssembly>

       <assemblyIdentity name=“Microsoft.VisualStudio.Package.LanguageService.10.0“ publicKeyToken=“b03f5f7f11d50a3a“ culture=“neutral“/>

       <bindingRedirect oldVersion=“2.0.0.0-11.0.0.0“ newVersion=“10.0.0.0“/>

</dependentAssembly>

We still have some outstanding work to test and make the binaries available for download and on Visual Studio Extension Gallery. Meanwhile, you can get the latest source (build 42258 as of now) and build it as follows:

  1. Install Visual Studio 2010 (C#) and the Visual Studio 2010 SDK. You must use Visual Studio 2010 to open the DAX Editor source as Visual Studio 2012 and above will upgrade the project.
  2. Open the DAX Editor source in Visual Studio 2010 and build the project.
  3. If you use Visual Studio 2013, fix the bindings as I explained above.
  4. Double-click the DaxEditor.vsix in the bin/debug folder and associated with the desired versions of Visual Studio you have on your machine.

The next thing on my list is to add support for Tabular default members and KPIs. The usual disclaimer applies that the sample is not supported and its features might not work with future editions of SQL Server. Other than this, enjoy it and I hope you find it useful as I do with my current projects.

 

Finding Duplicates in DAX

/5 Comments/in /by

A prerequisite for creating a relationship in Tabular/Power Pivot is to have a primary key column in the table on the One side of the relationship. This column must have unique values. If it doesn’t, the relationship won’t get created and you’ll get an error that the both tables have duplicate keys. If you have a relatively large table, it might be difficult to find the duplicates.

ID

Column1

1

Foo

2

Foo

1

Foo

 

However, given the above table design, you can add a simple calculated column to the table to return the count of duplicates for column ID using the following DAX formula

=CALCULATE (COUNTROWS(), ALLEXCEPT(Table1, Table1[ID]))

This expression uses the COUNTROWS() function to count the number of rows of Table1. Coupled with the CALCULATE function, this expression will be resolved in the context of every row. To ignore the column that you want to count on (ID in this case), you need to exclude it from the context, so that the row counting happens across the entire table for each ID value. Once the column is created, you can filter on it in the Data View to find out the duplicate rows with values 2, 3, etc.

Tabular M2M Relationships on the Horizon

/0 Comments/in /by

One of the biggest strengths of Microsoft self-service BI is the ability to create sophisticated data models on a par with organizational BI models built by professionals. This fact is often overlooked when organizations evaluate self-service tools and the decision is often made based on other factors but not insightful understanding of the data model capabilities. This is unfortunate because most popular tools on the market don’t go much further than supporting a single dataset. By contrast, Power Pivot allows you to import easily multiple datasets from virtually anywhere and join the resulting tables as you can do in Microsoft Access. This brings tremendous flexibility and analytical power.

Unlike multidimensional cubes, one of the limitations of the Power Pivot and Tabular data models has been the lack of support for declarative many-to-many relationships. The workaround has been using a simple DAX formula to resolve the relationship over a bridge table, such as =CALCULATE (SUM (Table[Column] ), <BridgeTable>) but this approach might present maintenance issues, as you have to create multiple calculated measures to support different slicing and dicing needs. However, as pointed out in my latest newsletter, the upcoming version of Power BI aims to remove adoption barriers and adds new features. And, one of this features, is bidirectional relationships and declarative support of M2M relationship, which Chris Webb already wrote about.

To test the M2M relationship, I attempted to create the same M2M scenario that I used in my book, which models a joint bank account. The corresponding Power Pivot schema is shown below. The CustomerAccount table is the bridge table that resolves the M2M relationship (a customer might have many accounts and a bank account might be shared by multiple customers). The Balances table stores the account balances over time and the Date table lets us analyze these balances over time.

010415_1902_TabularM2MR1

Setting up a M2M relationship in the Power BI Designer is achieved by changing the “Cross filter direction” relationship setting to Both. This setting and bi-directional relationships are described in more details here.

010415_1902_TabularM2MR2

Indeed, creating a report that shows balances by customer resolves the M2M relationship and aggregates correctly.

010415_1902_TabularM2MR3

Unfortunately, attempting to slice the report by Date returns an error in the preview version of the Power BI Designer so the M2M feature is still a work in progress. Brining this further, a useful addition could be declarative semi-additive functions to allow the user to set the aggregation behavior of the Balance measure, such as to LastNonEmpty. Similar to Multidimensional, this will avoid the need for user-defined explicit measures.

Absolute Tabular KPIs

/0 Comments/in /by

An interesting requirement popped up during an Analysis Services class I’m teaching this week. The customer wants to implement a Tabular/PowerPivot KPI that has this pseudo logic:

CASE

WHEN Actual Below Goal && Below LastYearSales Then Red

WHEN Actual Below Goal && Above LastYearSales Then Yellow

WHEN Actual >= Goal Then Green

END

 

Although I’m using Tabular, the same calculations will work with Power Pivot.

  1. Define a LastYearSales measure using the following formula assuming that the measure for the actual amount will be ResellerSales[SalesAmount]
    =CALCULATE(SUM(ResellerSales[SalesAmount]), PREVIOUSYEAR(‘Date'[Date]))
  2. Define a measure for the KPI actual value using the formula:

=SWITCH(TRUE(),

SUM(ResellerSales[SalesAmount])<Sum(SalesQuotas[SalesAmountQuota]) && SUM(ResellerSales[SalesAmount])<[LastYearSales],-1,

SUM(ResellerSales[SalesAmount])<Sum(SalesQuotas[SalesAmountQuota]) && SUM(ResellerSales[SalesAmount])>[LastYearSales],0,

SUM(ResellerSales[SalesAmount])>=Sum(SalesQuotas[SalesAmountQuota]),1

)

This measure using the DAX Switch function. However, to use it as a searched case expression, we use a trick where first argument returns TRUE and the rest of the conditions are evaluated against this hardcoded Boolean value. The formula normalizes the status as -1 (Red), 0 (Yellow), and 1 (Green).

  1. Define the KPI. Notice that the KPI uses an absolute value of 0 for the target.

    7356.kpi.jpg-550x0

    TIP: If you use Tabular, instead of using the UI you can open the BIM file source code, locate the _KPI Status measure and modify it as follows:

    CREATE MEASURE ‘ResellerSales'[KPI]=SWITCH(TRUE(),
    SUM(ResellerSales[SalesAmount])&lt;Sum(SalesQuotas[SalesAmountQuota]) &amp;&amp; SUM(ResellerSales[SalesAmount])&lt;[LastYearSales],-1,
    SUM(ResellerSales[SalesAmount])&lt;Sum(SalesQuotas[SalesAmountQuota]) &amp;&amp; SUM(ResellerSales[SalesAmount])&gt;[LastYearSales],0,
    SUM(ResellerSales[SalesAmount])&gt;=Sum(SalesQuotas[SalesAmountQuota]),1
    );
    CREATE MEASURE ‘ResellerSales'[_KPI Goal] = 0;
    CREATE MEASURE ‘ResellerSales'[_KPI Status] = if(ISBLANK(‘ResellerSales'[KPI]),BLANK(),
    If(‘ResellerSales'[KPI]&lt;0,-1,
         If(‘ResellerSales'[KPI]&lt;1,0,1)
    );