Articles in this series:
Installing the Server
The setup package installs three server components which include the Datazen Core Service, Data Acquisition Service and Rendering Service. All three services are installed and configured at once but you have the option to install each service separately and on different machines to scale-out the environment for security or performance reasons. My demo server has everything on one machine along-side SQL Server and Analysis Services. A document in the setup file extract named Security and Deployment Best Practices.docx contains additional production deployment guidelines.
Create a user to use for the service accounts. On my stand-alone box, this is a local user but could be a domain account in production. Create a SQL Server login for that Windows user with a database user in the db_datareader role for your data source (in our case, AdventureWorksDW2014). If using SSAS, create a role for the user or add it to the instance Administrators role for demonstration purposes.
Download and extract the zip file and then run the executable (currently named Datazen.Enterprise.Server.3.0.2908.exe).
If you want to allow users outside the firewall to access the server – which is necessary to use Internet connected mobile devices, you’ll need to configure IIS for external access. Using my Azure virtual machine this was a simple matter of adding an endpoint for port 80. In production, to use encrypted connections, you’ll need a certificate installed and enable access for port 443.
When prompted, enter a password for the Admin user. You’ll use this to log into the Control Panel.
The setup wizard supplies you with two important pieces of information that you’ll need for scale-out deployments and later configuration changes. Copy and paste the repository encryption key and the instance ID to a text file and store it in a secured folder.
Provide an optional host name and port or use the standard port 80 for server access. If you use a host header or non-default port, you’ll use these to access the server. Otherwise the server address is simply http://ServerName or assigned domain name. In the case of my Azure VM, the server address is http://ServerName.CloudApp.Net.
When prompted, enter an SMTP server address. This step isn’t required but this is the most convenient way to setup new users. When a user is created, the server sends an automated email message with a link and instructions for the user to provision their account and set their password. If you don’t have an SMTP server handy, you can copy and paste the user provisioning address and send it manually.
If you are setting up a user yourself, log out as Admin first and then use the provisioning link to set the password. Always use a strong password with mixed case letters, a number and special character.
Server Addresses
There are two web page addresses you should save to your favorites. The Control Panel is used to manage just about everything on the server. Fortunately, they’ve made this very simple. The Control Panel is: http://ServerName/CP and the HTML dashboard viewer is simply the server name which redirects to http://ServerName/Viewer.
Create a User
Login to the Control Panel as Admin using the password you provided. You should have one user designate as the hub owner. Think of this as the administrator user for a hub or group of dashboards. The first step is to create a Server User. Enter the user name and email address and then create the user. If you aren’t using the email option, click Set password link, follow the link and set the password.
After the new user is created and the password is set, add this user to the hub with the Users page. Check the Is Publisher and Is Owner boxes.
Create Data Sources and
Since the Datazen services are running under the identity of a service account that has access to each data source, this makes data sources easier to configure. Log out as Admin and log in as the hub owner user.
Create a new data connection, choose the data provider (I’m using SQL Server) and provide a name. For the Data Source, enter the server or instance name and the name of the database for the Initial Catalog. Since the service account has read access to the database, don’t provide a user name or password and check the Integrated Security box. When you click Test Connection, it should succeed and you can click Next to give permission to group members. You can just use the Everyone group for now and then add user groups in the future.
Click the name of the data connection to add a data view. This is the query that will drive a dashboard or KPI. Click the New Data View button and write the query in the Data View Query box. I prefer to create queries in SSMS and then paste the script after debugging and checking the results. I’ve created a data view named Internet Sales 2014 using the following T-SQL script:
Select
sum(f.OrderQuantity) OrderQty,
sum(f.[SalesAmount]) SalesAmt,
sum(f.[TaxAmt]) TaxAmt,
sum(f.[Freight]) Freight,
p.EnglishProductName Product,
ps.EnglishProductSubcategoryName Subcategory,
pc.EnglishProductCategoryName Category,
st.SalesTerritoryCountry,
st.SalesTerritoryGroup,
st.SalesTerritoryRegion,
od.CalendarYear,
od.EnglishMonthName MonthName,
od.MonthNumberOfYear,
od.FullDateAlternateKey DateValue
from
[dbo].[FactInternetSales] f
inner join [dbo].[DimDate] od on f.OrderDateKey = od.DateKey
inner join [dbo].[DimProduct] p on f.ProductKey = p.ProductKey
inner join [dbo].[DimProductSubcategory] ps on p.ProductSubcategoryKey = ps.ProductSubcategoryKey
inner join [dbo].[DimProductCategory] pc on pc.ProductCategoryKey = ps.ProductCategoryKey
inner join [dbo].[DimSalesTerritory] st on f.SalesTerritoryKey = st.SalesTerritoryKey
where
od.CalendarYear = 2014
group by
p.EnglishProductName,
ps.EnglishProductSubcategoryName,
pc.EnglishProductCategoryName,
st.SalesTerritoryCountry,
st.SalesTerritoryGroup,
st.SalesTerritoryRegion,
od.CalendarYear,
od.EnglishMonthName,
od.MonthNumberOfYear,
od.FullDateAlternateKey
The next page should show a preview of the results. Click Finish to complete and save the data view.
I’ll create one more data source and data view for Analysis Services. When I create a new data source using the Microsoft SQL Server Analysis Services data provider, this adds MSOLAP for the Provider property. This will work for a multidimensional or tabular database. Give the connection a name, enter the server or instance name for the Data Source property and enter the database name for the Catalog. As with the SQL Server data source do not enter a UserName or Password because the service account already has read access to the database. Click Next, make sure the Everyone group has access and then click Finish to save the data source.
Click on the new data source and then click New Data View… I’m creating a data view query named Daily Reseller Sales by Category and Country using the following MDX query script. Once again, I’ll write, debug and test this in SSMS and then copy the query to the Data View Query box.
with
member measures.DateValue as
CDATE([Date].[Date].CurrentMember.MEMBER_CAPTION)
member measures.DateName as [Date].[Date].CurrentMember.Name
member measures.MonthName as [Date].[Month of Year].CurrentMember.Name
member measures.CategoryName as [Product].[Category].CurrentMember.Name
member measures.CountryName as
[Sales Territory].[Sales Territory Country].CurrentMember.Name
select
{
measures.DateValue,
measures.DateName,
measures.MonthName,
measures.CategoryName,
measures.CountryName,
[Measures].[Reseller Sales Amount],
[Measures].[Reseller Gross Profit]
} on Columns,
(
[Date].[Date].[Date].Members,
[Date].[Month of Year].[Month of Year].Members,
[Product].[Category].[Category].Members,
[Sales Territory].[Sales Territory Country].[Sales Territory Country].Members
)
on Rows
from [Adventure Works]
where
{[Date].[Calendar Year].&[2012], [Date].[Calendar Year].&[2013]}
A couple things…
Datazen consumes MDX differently than some tools and in order for the dimension members I put on the Rows axis to be included in the query results, it was necessary to create calculated members and then add these values on the Columns axis. There are other techniques but this is the most flexible method I’ve found.
The Time Navigator dashboard control requires a true Date value and this is why I created the DateValue calculated member using the CDATE function and MEMBER_CAPTION property. When the designer detects the right data type it will enable data range filtering.
Resources
Datazen Enterprise Server download from Microsoft
http://go.microsoft.com/fwlink/?LinkId=529426
Datazen Get Started, Documentation and Resources
