Wednesday, August 17, 2016

EnableRolapDistinctCountOnDataSource

We are digging into SSAS ROLAP, and based on our research, the setting that enables the database engine to manage distinct counts should be updated to 1. To adjust this feature, you have to modify the ini file and then restart the SSAS engine.

Why we made the change


Our research found two key postings, both respected SQL Server professionals:

In 2014, Karen Gulati did exploratory ROLAP work using SQL Servers new column store indexes. One key finding was to update the EnableRolapDistinctCountOnDataSource setting to 1. See: Harnessing the Power of both worlds.

Also in 2014, Hilmar Buchta did some work with SSAS ROLAP against an MSFT Parallel Data Warehouse, and determined to update the setting.
See:  Parallel Data Warehouse (PDW) and ROLAP - Hilmar Buchta.

Summary

  1. Locate msmdsrv.ini
  2. Make a backup of the original, unaltered ini file. (backups are our friend)
  3. Locate <EnableRolapDistinctCountOnDataSource>
  4. Change the setting to 1
  5. Save the file and restart SSAS

Details

msmdsrv.ini

Locate msmdsrv.ini and make a backup. Typically it is located in the folder: 
C:\Program Files\Microsoft SQL Server\MSASxx.MSSQLSERVER\OLAP\Config
(where xx is the version)

Make the Change

Locate the entry for EnableRolapDistinctCountOnDataSource, make the change and save the file.
With the change made, restart the SSAS service.

Before
       
    <EnableRolapDistinctCountOnDataSource>0</EnableRolapDistinctCountOnDataSource>

After
       
   <EnableRolapDistinctCountOnDataSource>1</EnableRolapDistinctCountOnDataSource>

FIX: SSAS 2012 to back-end SQL Server 2014

We are not using this configuration, but since we did stumble upon it, we just wanted to make a note of it in case any readers are using this configuration.



Tuesday, August 16, 2016

Free Power BI eBook Manual

Microsoft Press is offering a free ebook on Power BI. And at 176 pages, it is not a lightweight book. Starting off with an introduction that covers the basics, it goes deeper into how to share the dashboard, refreshing the data, using Power BI Desktop and building a data model.  It called Introducing Microsoft Power BI and its worth downloading and using as a daily reference. You can find it here:  Free ebook: Introducing Microsoft Power BI.

Monday, August 15, 2016

ROLAP Polling Query - use a change tracking table

Setting up the polling query for our real-time ROLAP cube against an EXASOL database, we used an easy, but very bad practice approach.  We set our polling query directly against a fact table. Nice and easy for testing and proof of concept. Production? don't consider using it.

Polling a count against a small or medium size fact table might won't create much of a problem. When the dataset is huge it might take more resources.  So, when the data set is really large, and our goal is very near real time, why not poll a very small table.

Data Change Table

Let's call it TrackDataChange.  A minimalist version will have just a few columns:  the source system that loaded the data, the load date and a primary key ID. The only difficult part is you have to configure your ETL process(es) to insert a single row to your new TrackDataChange table. The new record simply marks that the ETL process has completed.


With the TrackDataChange table in place, and your ETL processes configured to insert a row after inserting new fact table table, your polling query will look like this:

        SELECT COUNT(*) FROM DWTEST.TRACKDATACHANGE;

You'll probably want more than just the basic columns. The key point is to run SSAS's polling query against an independent table dedicated to tracking fact table changes.

Pros

the polling query avoids data tables used for data analysis
the TrackDataChange table is narrow and holds few records
the TrackDataChange table is quick to query
the TrackDataChange table is quick to insert into

Cons

the ETL process is marginally more complex

Thursday, August 11, 2016

Database Lifecycle Management

On its website, Redgate has posted an overview on Database Lifecycle Management.  It's a real problem, and one that has note been completely solved.  Application code can be ripped out, and replaced wholesale.  Not so with the database. While there are bits of what we might call code, the database is really the data, with a small bit of code. But once setup, it's live.  There are no cut and paste approaches.  Changes have to be well understood, and implemented with caution.  Redgate's whitepaper can be found here:  Solving the database deployment problem with DLM.

They also have a free DLM tool, called DLM Dashboard. while it is part of the full SQL Toolbelt, you can download just the DLM Dashboard for free.  Redgate is a great operation, putting a lot of effort into supporting SQL Server, including its sponsorship of http://www.sqlservercentral.com/.
You can find the DLM Dashboard here:  http://www.red-gate.com/products/dlm/dlm-dashboard/.

Book: R Programming for Data Science

Author and teacher Roger D. Peng has published R Programming for Data Science. You can find it here at Leanpub/rprogramming.  Mr. Peng has been using and teaching R since 1998 (almost 20 years) and his book provides not just a good book on R, but also thoughtful insight into just why R works the way it does, and how to take advantage of R.

Videos

OK, need another reason to consider getting this book?  All of the sections and chapters have embedded links to YouTube videos. 

Monday, August 8, 2016

Visio Shapes (vssx) on TechNet

You can find a long list of Visio shapes, symbols and icons on TechNet for Visio 2016/2013. It's called the Integration Stencils Pack and you can find it here:  Microsoft Integration Stencils Pack for Visio 2016/2013.

Using Power BI to query EXASOL via ROLAP

You're using Power BI against an EXASOL analytic database. Unfortunately, the only connection currently supported uses ODBC (as of August, 2106). ODBC is a stable connector, but is generally considered to be slow. A native connector would be ideal. 

Power BI does have native connectors to other systems, so might an EXASOL connector be in the pipeline?  Going to the Power BI Ideas section, where new features are requested and voted on, you do find a posted suggestion to create an EXASOL Direct Query Connector. (Power BI ideas - EXASOL Direct Query Connector). Unfortunately, it only has a nominal number of votes. Fortunately, you do have an alternative.

Virtual Data Mart - using ROLAP




EXASOL has an OLAP Connector for SSAS that allows Microsoft shops to create virtual data marts. Using SSAS's real-time ROLAP protocol, you can setup a virtual data mart against an EXASOL analytic database. SSAS's ROLAP provides an intermediate bridge between Power BI and your EXASOL database. This has multiple benefits

  • Curate analytic data into targeted virtual analytic cubes (or single if need be)
  • Add a layer of security, when needed
  • Push processing down to the analytics database engine

We've already taken a look at SSAS ROLAP cubes using EXASOL.  You can find the details here:  Build an SSAS ROLAP Cube using EXASOL.