Plotting Goal in Power BI

A customer wanted a bar chart where each bar shows the invoiced and realized sales and a marker to show a goal, as sketched below (notice the Budget marker). The chart had to render the data from SSAS.


In SSRS, this of course can be done using a bullet gauge, which I hope one day will make its way to Excel and Power View. Because Excel PivotChart doesn’t support XY Scatter charts when connected to SSAS, the compromise is to use a column chart, such as the one below, where the goal was plotted as a line chart with the line hidden and only markers visible.


The closest Power View compromise would be the chart below. This requires the latest Power View build which is only available in Power BI 2.0. In Power BI, Power View supports a Combo Chart Stacked variation.


First Look at Datazen

UPDATE: 1/1/2016   As Microsoft announced in the public BI roadmap (, Datazen will be fully integrated with SSRS 2016 for delivering mobile reports, alongside paginated (traditional SSRS reports), and Power BI Desktop reports.

To follow up on my blog announcing Datazen, I want to share some additional notes now that I had to chance to install it and take it out for a spin.


  1. Easy installation experience – Installation can’t be easier either although I’m not crazy about the Datazen decision to make the Publisher available in the Windows Store. I just don’t like Windows Store applications, not the mention that they don’t work by default with Windows Server (you need to enable the Desktop Experience feature and use a non-admin account). To share your dashboards, you can publish them to a server component (Datazen Enterprise Server). The server installs as an ASP.NET application running under IIS (no surprises here). The server supports custom authentication, where users are stored locally or integrate with Active Directory.
  2. Easy authoring experience – Designing dashboards with Datazen is a no-brainer. The tool supports the basic visualization blocks, including filters, charts, gauges, three maps, maps (customization with ESRI shapes is possible) and data grids. I’d like the idea to snap visualizations to a grid. When you expand the visualization height or width, the Datazen Publisher realizes that it has more real estate and adds additional elements to the visualization.
    To see how this work, take a look at this video.
  3. Designed for mobile – Everyone wants mobile rendering nowadays but it’s important to agree on what the desired experience should be. Mobile devices range from tablets, which have larger displays, to phones which are much smaller. Responsive web design seems to be compromise but it’s typically limited to reflowing content based on the display capabilities of the device. Rightly so, Datazen allows the designer to optimize the user experience by offering options for Tablet and Phone. The designer doesn’t have to start from scratch when targeting another device. Instead, the designer can use the visualizations already added to the dashboard and just resize and rearrange, or don’t add them at all. The designer supports pre-defined themes. There are native viewer applications for Windows, Windows Phone, iPhone, and Android.042015_0153_FirstLookat2
  4. Good data support – Out of the box, Datazen supports the most common data sources, including Excel files, SQL Server, Analysis Services, Oracle, ODATA, Web Services. Additional data sources can be plugged in via ODBC drivers. The designer can decide to import the data and refresh it periodically or create a connection to the data source and execute live queries. Another feature that I like is that during the design phase the designer auto-generates data so the dashboard can be designed entirely offline. Data can be plugged in later. Live queries can be parameterized. When SSAS is targeted, the user identity can flow to the data model for row-level security using Kerberos or EffectiveUserName. Live queries are configured by implementing server-side views which are conceptually similar to SSRS shared datasets. I didn’t see an option to import SSAS KPIs as PerformancePoint allows you to do.


  1. No data exploration – Similar to SSRS, the query is fixed at design time. Therefore, the user cannot change the report layout or add/remove fields. In this age of interactivity, I consider this a major shortcoming. True, the out-of-the-box dashboards are effective and there is more interactivity then SSRS reports, such as the user can slide the finger to select multiple date periods and the page doesn’t repost. Other than that though there is nothing that Datazen can do than SSRS can’t.
  2. Great front, weak back– It’s obvious that the Datazen developers have focused exclusively on the presentation layer. I didn’t see alerts, subscriptions, printing, exporting, execution log, etc.
  3. Limited customization – I didn’t see options for expressions, conditional formatting, localization, etc.
  4. Drillthrough capabilities are limited to initiating a drillthrough on the entire row instead of more granular cell-level drillthrough.
  5. Confusing integration story – Honestly, I’m not sure why Microsoft acquired this tool and this acquisition is a surprise to me. I’d prefer Power BI v 2.0 on premise with shorter release cycles and more features than yet another visualization tool that has a problem fitting in. I really hope it gets integrated with Power View at some point to reduce confusion.

If your primary visualization goal is implementing basic dashboards that target mobile devices with minimum design effort, Datazen might do the trick. For demos, take a look at (use datazen for user name and password).

Seeking Oracle Reloaded

Scenario: You create an SSIS 2012 project in SSDT that uses the Attunity Oracle connector. By default, Attunity creates a private connector. You verified that the package connects and executes successfully. Then, you promote connector to a project-level connector so that you can manage the credentials in the SQL Server job configuration across all packages. When you attempt to deploy to the SSIS catalog, you get the following error:

Failed to deploy project. For more information, query the operation_messages view for the operation identifier <X>

When you query the operation_messages view for that message_id, you get the enlightening reason:

Failed to deploy the project. Fix the problems and try again later.:Unable to create the type with the name ‘MSORA’.

Solution: I had my own fair share of Oracle OLE DB driver installation issues, some of which I documented here. I don’t know how this customer managed to mess up the Oracle driver installation (not that it’s hard to do) but the registry settings were off. How do we fix this horrible issue?

  1. Fix the Oracle driver installation.
  2. Remove and reinstall the Attunity drivers. It could be that in this particular case, the customer had both the Attunity 1.2 and 2.0 drivers and they removed the 1.2 version.

Note that you must install both 32-bit and 64-bit Oracle and Attunity drivers. That’s because SSDT (being a 32-bit application) users 32-bit drivers, while the SSIS runtime uses the 64-bit drivers unless you specifically configure the job to run as 32 bit.

Microsoft Acquires Datazen to Bring On-premise Mobile BI

Microsoft announced today that it acquired Datazen Software. This is a great news because an on-premise mobile dashboard solution has been a big pain point with Microsoft BI. SharePoint Excel Services and Reporting Services are mobile-friendly but Power View is still Silverlight-based. And, customers have been skeptical about the time it will take for Microsoft to deliver a true mobile-ready on premise solution that complements its cloud-based Power BI.

I haven’t personally used Datazen but its looks very promising especially considering that “In particular, SQL Server customers love Datazen, because it is optimized for SQL Server Analysis Services and the overall Microsoft platform, enabling rich, interactive data visualization and KPIs on all major mobile platforms: Windows, iOS and Android”. And, the price is right: “As of today, SQL Server Enterprise Edition customers with version 2008 or later and Software Assurance are entitled to download the DataZen Server software at no additional cost. This means millions of people around the world will now be able to visualize and interact with data on their mobile devices, using the native mobile apps available at no charge at the respective app stores.”

The Datazen architecture is server-based and installs on Windows Server IIS.

Implementing Conditional Formatting in Tabular

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.


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.


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



       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;



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:



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

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

       END SCOPE;



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.


    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*/);


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