First Look at Datazen

/1 Comment/in /by

UPDATE: 1/1/2016   As Microsoft announced in the public BI roadmap (http://blogs.technet.com/b/dataplatforminsider/archive/2015/10/29/microsoft-business-intelligence-our-reporting-roadmap.aspx), 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.

Pros

  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.
    042015_0153_FirstLookat1
    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.

Cons

  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 demos.datazen.com (use datazen for user name and password).

Seeking Oracle Reloaded

/0 Comments/in /by

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

/0 Comments/in /by

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

/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.

 

Power BI vNext SSAS Connector and Security Reloaded

/0 Comments/in /by

To follow up on my previous post on the same subject, a customer was eager to jump on the Power BI->On Prem SSAS bandwagon and try the simplified security model. But “omne initium difficile est” (every beginning is difficult). Their SSAS server was installed on a domain acme.com while their e-mail addresses were using a different scheme, e.g. user@contoso.com, although both server and accounts were under the same acme domain. As we’ve quickly found out, Active Directory had an issue with this setup which manifested with the following error in the SQL Server Profiler connected to SSAS.

“The following system error occurred: The name provided is not a properly formed account name.”

If you see this error, follow these steps to confirm the issue:

  1. Remote in to your SSAS server.
  2. Open Command Prompt and enter:
    Whoami/upn
  3. If you see that your login domain name is different that the e-mail you use to sign up to Power BI, you’ll have the same issue. For example, your e-mail address might be user@contoso.com while whoami might show user@acme.com.

The workaround is to bridge the two identity schemes by adding an alternative UPN suffix in Active Directory Users and Trusts, as Greg Galloway explained here. This of course will require help from your AD administrator (one of the most important persons to befriend on your BI quest) so some coercing is to be expected.

Thanks to Jen Underwood, Will Thompson, Mini Nair and Giri Nair from Microsoft who helped us troubleshoot the issue.

Projecting a Scalar Value with DAX EVALUATE Queries

/0 Comments/in /by

When you work on more complicated DAX measures, you should get out of the Power Pivot or BISM design environment and use the excellent DAXStudio (or SSMS) with the EVALUATE query syntax. But then eventually the measure would return a scalar value while EVALUATE requires a table. You can use the DAX ROW function to create a single-row, single-column table, and then show the result using EVALUATE.

030415_1409_Projectinga1

Power BI vNext SSAS Connector and Security

/0 Comments/in /by

As you’ve probably heard, Power BI vNext will allow you to keep your data on premises in SSAS MD and Tabular data models while your reports in the cloud can connect to these data models on premises. Currently, the Analysis Services connector support only Tabular models while plans for MD have been announced as well. Currently, SSAS understand Windows security only and you might wonder what needs to be put in place for security to work.

Interestingly, if your users have used their work e-mail addresses to sign up for Power BI and your company is using Active Directory, you don’t need do synchronize your AD with Azure using DirSync. That’s because Power BI will pass the user identity on the connection string using the EffectiveUserName property. Consequently, connectivity and data security will work as usual. On the other hand, if users used an .onmicrosoft.com e-mail address, DirSync is required. Microsoft explains this in more details in the “Why you might need use DirSync to connect to an on-premises Analysis Services Server” document.

While this definitely simplifies the effort to get Windows security working, it doesn’t help if custom security, such as Forms Authentication, is required, such as to integrate Power BI with custom applications. Embedding Power BI reports on custom applications and supporting custom security are on the Power BI top wish list and I hope Microsoft will support this scenario soon as well.

About Gartner Magic Quadrant 2015 for BI

/0 Comments/in /by

The 2005 Gartner Magic Quadrant is out and according to Gartner, the distance between Tableau and the other leaders is widening. Here is the full report. It’s obvious that Gartner focuses only on the self-service aspect of BI and throws away the entire gamut of tools required to deliver successful BI solutions, including RDBMS, ETL, data models, MDM, etc. But even if we focus on self-service BI, I don’t quite agree with Gartner’s infatuation with Tableau (see my blog “Top 10 Reasons for Choosing Microsoft Self-service BI”). It’s a good visualization tool but based on what I hear, people tend to overestimate its capabilities and get in trouble. Nevertheless, for the most part I agree with the Gartner’s assessment related to Microsoft BI cautions, except:

1. “Microsoft had the highest percentage of customer references citing absent or weak functionality (for example, no drill-through capabilities in Power View) as a platform problem.”

Really? The other vendors have more functionality? Can Tableau import multiple datasets, transform data, have Q&A, scale, share and discover datasets, or have data governance? And, that percentage is probably high, because the percentage of customers using Microsoft BI is high.

2. “However, customers may have difficulty finding external resources with experience in the newer Power BI stack, which requires a different set of skills and expertise than Microsoft’s sweet spot of systems-of-record, developer-focused BI deployments.”

The Power BI stack shouldn’t require that much knowledge.

A successful BI strategy should be much more than just “putting lipstick on a pig”. No matter how nice the lipstick is, it’s still a pig. However, the Gartner’s summary of the Microsoft weaknesses is spot on:

“Microsoft is attempting to address many of these limitations in the forthcoming stand-alone version of Power BI, which does not require Office 2013 or an Office 365 subscription and can access Analysis Services structures and content without physically moving underlying enterprise data to the cloud.”

I’m looking forward to the 2016 quadrant. For now, I like better the Forester Research report (http://www.forrester.com/pimages/rws/reprints/document/115485/oid/1-RN6A25).

022515_0242_AboutGartne1