Data definition in Get Started with Datazen and Analysis Services

Article
04/15/2016

Applies To: SQL Server 2016

Welcome to the second task in the Get started with Datazen and Analysis Services. The steps in this task are performed in the Datazen Control panel. If you just completed the server configuration, Control Panel should still be open in a browser window.

Create a data source for an SSAS cube

After you create a hub in Control Panel, you will see an additional set of options on the lower left navigation pane, including Data Sources. This task creates a data source connection to the AdventureWorksDW2014-multidimensional database.

In the Datazen Control Panel, click Data Sources on the left navigation pane.
Click New Data Connection.
Choose Microsoft SQL Server Analysis Services and give the data connection a name.
Skip the personalization option. We are not configuring per-user data access permissions in this walkthrough.
In Data Source, enter the network name of the computer running SSAS or its IP address. If using a named instance, the format is \.
In Catalog, enter the name of the Analysis Services database.
Leave UserName and Password blank.
Click Add a property and choose Integrated Security, with SSPI.
Click Test Connection to confirm the connection succeeds. A successful result indicates that the Data Acquisition service account, which has admin rights on SSAS, connected to Analysis Services.
This next step sets the authorization policies on the data source. You can choose the Everyone user group for open access to the SSAS data source, which might be acceptable for testing purposes. Datazen includes the Everyone group as a built-in option so that you can start testing with fewer steps. With this permission setting, anyone in your domain connecting from Datazen Publisher can use the SSAS data source you just created as a data source for their dashboards.

Otherwise, you can choose the individual users that you defined during user registration.
Refresh the Control Panel to see the data source you just created.

At this point, you might want to create additional data sources for SSAS tabular and SQL Server relational databases, assuming you attached them to local SQL Server instances earlier on.

Create a data view for an MDX query

Once the data source is created, you’ll need one or more data views as a container for the MDX. Sometimes it’s easier to create multiple discrete data views that can be imported together into a dashboard. Because data relationships in the model are based on lookups, your original queries can be simple as long as there are values in common that can be used to connect the data views on import.

In this walkthrough, however, all of the data is provided in a single MDX query.

Develop the MDX query in SSMS

If you are running Datazen Server version 3.0.3305 or later, you can paste in MDX queries that you generate in SQL Server Management Studio (SSMS) or Report Builder. This task builds an MDX query in SSMS.

In Management Studio, connect to the SSAS multidimensional instance that has the AdventureWorksDW2014 database containing the AdventureWorks cube.
Right-click the database | New Query | MDX to open an MDX query window.

Copy the following query into the window:

WITH 
 MEMBER [Measures].[Total Sales] as ([Measures].[Internet Sales Amount] + [Measures].[Reseller Sales Amount])

SELECT
  {
  [Measures].[Total Sales], 
  [Measures].[Internet Sales Amount],
  [Measures].[Reseller Sales Amount]
  }
  ON 0,
NON EMPTY
CROSSJOIN([Sales Territory].[Sales Territory Country].children, [Product].[Category].children, [Date].[Month of Year].children)
ON 1
FROM [Adventure Works]
WHERE [Date].[Calendar Year].&[2013]

Execute the query to make sure it returns the results you expect. It should look similar to the following partial screenshot:

If you check the Messages tab in the query output window, you should have gotten back 283 rows and 6 columns.

An alternative approach to building queries in SSMS is to right-click Browse and create your query using the query designer. You can learn more about this approach from New datazen server brings some MDX magic (blog post).

Create a data view in Datazen Control Panel

In Datazen Control Panel, click Data Sources.
Click the SSAS data source you just created, and then click New Data View in the upper right corner.
Give the data view a name and then paste in the MDX query you just ran in SSMS.
Edit the display names to friendlier names that will look better in a dashboard.
View the final result.

After you click Finish, the data view you just created shows up in the Data Views list of the current data source.

List of data views in Control Panel

Next step

At this point, you are ready for the final task: designing the dashboard in Datazen Publisher on a Windows client.