Get started with Datazen dashboards using Analysis Services data and MDX queries
Applies To: SQL Server 2016
Device-agnostic Datazen dashboards can help you understand and analyze data from a wide variety of data sources, including SQL Server Analysis Services (SSAS) multidimensional and tabular datasets. In this walkthrough, you’ll learn how to configure servers, data sources, and ultimately build a dashboard similar to this one.
This dashboard leverages data from the AdventureWorks sample cube to return a rowset that can be consumed in a Datazen data model. It consists of a bubble map, category chart, and several selectors for filtering sales details by month, country, and product category. A single data view on the Datazen Server provides the data.
The instructions in this walkthrough will work with any supported version of Analysis Services. Using a SQL Server 2016 build is not a requirement.
Using Multidimensional data in a Datazen dashboard
Before you start, it’s a good idea to check any assumptions you might have about using SSAS data in a dashboard. Datazen does not provide native support for SSAS cubes or tabular models. Instead, it generates its own data model that uses value-based lookups to establish data relationships among tabular data structures, thus enabling a uniform experience for each control regardless of the data’s origin.
When working with SSAS as a Datazen data source, think of your MDX query as a mechanism for generating a flat rowset for a new Datazen model that you build in Publisher. If you keep this fundamental point in mind, it will help alleviate any confusion around SSAS feature availability (or lack thereof) in Datazen. To drive this point home, we’ll call out several SSAS features that you should not expect to work in Datazen.
Unsupported SSAS features
Aggregations, hierarchies, and KPIs that you use in a dashboard are implemented in the Datazen data model and not in SSAS. There is no facility for dynamically changing the MDX query at run time. Whatever MDX query you specify in the data view is used as-is on first use and on subsequent data synchronizations. Other SSAS features that do not transfer to Datazen include the following:
SSAS dimension hierarchies
SSAS custom rollups