Microsoft Analysis Services using ROLAP storage in Teradata.

  • Article

I recently had the opportunity to work with a customer who was interested in implementing a Microsoft SQL Server 2012 Analysis Services database, using ROLAP storage, with Teradata as the relational data source. With a week to build a Proof of Concept (POC) solution using Microsoft SQL Server Analysis Services database and having had previous encounters with Analysis Services using ROLAP storage with Teradata as the source relational database, I was headed into familiar territory.  Knowing that MOLAP storage typically provides faster query response, my first thought and initial objective was to try to steer the customer away from ROLAP storage and toward MOLAP storage.

I’ll state here that I have a definite bias towards MOLAP storage and a somewhat stronger bias toward MOLAP when any relational data source is being used in conjunction with a Managed Code OLE DB provider. The reasons for that bias are related to query performance and rest with two primary considerations. The first is that regardless of the source of the relational data, MOLAP storage typically performs significantly better than ROLAP storage. The second basis for my bias is that in 64-bit environments, when connecting to Teradata, the available Teradata OLE DB provider is a .NET Managed code provider. Because this is a managed code OLE DB provider being used in a native code application (Analysis Services), there is a significant amount of additional marshalling that must occur to handle things like .NET garbage collection. The additional marshalling that must occur results in somewhat slower performance than is the case with native code OLE DB providers when retrieving data from the relational data source.

After a reasonably short conversation with the customer, it was readily apparent that the focus of interest was minimizing processing times and having data available in real-time or near real-time. Given that MOLAP storage would have required very frequent processing with inevitable latencies, it quickly became apparent that ROLAP storage would necessarily be part of the solution. So much for the original action plan and now I had serious questions about whether or not this POC could be completed in the time span of one week. Now it was time to either concede defeat or come up with a new plan of attack.

Having been down this path previously, I had a few resources from which to draw:

  1. I’ve dealt with similar scenarios multiple times in the past, so I know what potential pitfalls exist.
  2. I’ve been involved with support for Microsoft SQL Server Analysis Services since 2000.
  3. I’m more familiar than I probably should be with the whitepaper Improve your OLAP Environment With Microsoft and Teradata, written by Rupal Shah and Richard Tkachuk in 2007.

While the whitepaper by Shah and Tkachuk was written in relation to a specific implementation, it does list a number of well-defined actions which provide significant performance benefits in a ROLAP environment. Those include the following:

  1. Use normal ETL Processing to load or update base tables in an Enterprise Data Warehouse
  2. Build a Semantic Layer to contain analytic specific content with the following characteristics:
    1. A Star or Snowflake schema.
    2. Primary and Foreign Keys defined as not nullable and not compressible.
    3. Dimension table Primary Keys use the UNIQUE constraint.
    4. Use ID rather than Name or Description columns as Primary Keys.
    5. Ensure that measures in any Fact Tables are a numeric data type.
    6. Implement Referential Integrity on Primary Key/Foreign Key columns.
    7. Consider creation of secondary indexes on FACT columns.
    8. Take advantage of partitioning with Partitioned Primary Indexes to improve performance.
  3. Create Teradata Aggregate Join Indexes (AJIs) to facilitate delivery of data in a timely manner.
  4. Check the relational queries generated by Analysis Services against the defined AJIs and check the request using the Teradata Explain command to ensure that the AJI is included in the query plan.

Since using ROLAP storage will result in relational queries being sent from Analysis Services to Teradata, all of that makes a great deal of sense. At this point, one could confidently open SQL Server Data Tools (SSDT) and begin designing an Analysis Services database using Teradata as the data source and live happily ever after. Unfortunately, there are still a few things that Shah and Tkachuk didn’t really touch on in their whitepaper. One of the first things that we want to do is configure the server such that it’s in a state that is consistent with information from the SQL Server 2008 R2 Analysis Services Operations Guide. Granted, this is a bit dated and some of the configuration settings are different in the SQL Server 2012 and 2014 releases (particularly the MemoryHeapType and HeapTypeForObjects settings).

  1. Out of the box, Microsoft Analysis Services is configured and optimized for SINGLE USER QUERY EXECUTION. Essentially, that means that it’s quite possible for a single long running query that is bottlenecked in Storage Engine (in this case retrieving data from Teradata) can potentially block other
    queries. To change that and configure the server for MULTI-USER QUERY EXECUTION:

    1. Locate the msmdsrv.ini file (default location is C:\Program Files\Microsoft SQL Server\MSASXX.MSSQLSERVER\OLAP\Config for a default install and C:\Program Files\Microsoft SQL Server\MSASXX.<INSTANCE_NAME>\OLAP\Config for a named instance).

    2. Create a backup of the file, just in case you generate malformed XML while editing the file.

    3. Open the file with NotePad or some other text editor and find the following XML tags:

      <CoordinatorQueryBalancingFactor>
      <CoordinatorQueryBoostPriorityLevel>

    4. Change the values FROM:

      <CoordinatorQueryBalancingFactor>-1</CoordinatorQueryBalancingFactor>
      <CoordinatorQueryBoostPriorityLevel>3</CoordinatorQueryBoostPriorityLevel>

      TO:

      <CoordinatorQueryBalancingFactor>1</CoordinatorQueryBalancingFactor>
      <CoordinatorQueryBoostPriorityLevel>0</CoordinatorQueryBoostPriorityLevel>

  2. Locate the <DataStorePageSize> and <DataStoreHashPageSize> XML tags in the msmdsrv.ini file and change FROM:

    <DataStorePageSize>8192</DataStorePageSize>
    <DataStoreHashPageSize>8192</DataStoreHashPageSize>

    TO:

    <DataStorePageSize>65536</DataStorePageSize>
    <DataStoreHashPageSize>65536</DataStoreHashPageSize>

  3. If you happen to be working with a Microsoft SQL Server 2008 or 2008 R2 instance of Analysis Services, I’d strongly recommend upgrading to 2012 or 2014. If upgrading a 2008 or 2008 R2 instance isn’t an option, then you’ll want to:

    1. Locate the following XML tags in the msmdsrv.ini file

      <MemoryHeapType>
      <HeapTypeForObjects>

    2. Change the values FROM:

      <MemoryHeapType>1</MemoryHeapType>
      <HeapTypeForObjects>1</HeapTypeForObjects>

      TO:

      <MemoryHeapType>2</MemoryHeapType>
      <HeapTypeForObjects>0</HeapTypeForObjects>

  4. Save then close the edited msmdsrv.ini file.

  5. Stop and restart the Analysis Server.

 

To get maximum query performance, it’s going to be necessary to build appropriate AJIs in Teradata and ensure that the statistics are current. Now it’s time to consider some of the things that your mother never taught you about working with Analysis Services using ROLAP Storage with Teradata as the relational source. A close working relationship with the Teradata DBA(s) becomes essential because query patterns tend to change over time. That means that the AJIs in the Teradata semantic layer must be periodically tuned. If the environment is such that users are accessing data in non-parameterized reports and not executing ad-hoc queries, then it’s quite easy to capture the Progress Report End events in a Profiler trace or via Teradata DBQL access logs. Things become a bit dicey when users are viewing data in interactive reports or are executing interactive queries that apply filters and this is where things get interesting.

When users are interacting with the data in parameterized reports or executing ad-hoc queries that apply filters, Analysis Services submits a set of one or more parameterized SQL statements to Teradata for execution. When the queries are sent to Teradata via the OLE DB provider, the Parameters are represented by a “?” as a parameter placeholder. This makes tuning the AJIs somewhat challenging in that neither the OLE DB provider nor the Teradata DBQL Logs expose the actual parameter values. Without knowing which partitions are being accessed, tuning the AJIs becomes pure guess work.

You can identify long running SQL statements relatively easily by:

  1. Start a Profiler trace to capture the Query Begin, Query End, and Progress Report End events. 

  2. Run the set of MDX queries that generated poorly performing parameterized SQL queries.

  3. Save the Profiler trace to a table in SQL Server

  4. Execute the following SQL Query:

    SELECT ROWNUMBER, EVENTCLASS, EVENTSUBCLASS, TEXTDATA, DURATION FROM
    <TABLE_NAME> WHERE EVENTCLASS=6 AND EVENTSUBCLASS=25 ORDER BY DURATION
    DESC

While that will give you some information regarding long running SQL statements, it will not provide information related to actual parameter values. In order to capture the actual  parameter values that are being passed from Analysis Services to Teradata, we have to make a change to the cartridge that is used by Analysis Services when connecting to a Teradata server. NOTE: Everything you are going to read from this point on is totally unsupported by Microsoft.

 

  1. Locate the trdtv2r31.xsl file (default location for a default installation is C:\Program Files\Microsoft SQL Server\MSAS11.MSSQLSERVER\OLAP\bin\Cartridges)

  2. Make a copy the trdtv2r31.xsl file from the cartridges subdirectory and save it to another directory in unedited form.

  3. Manually edit the trdtv2r41.xsl cartridge file in the cartridges subdirectory and change
    FROM:

    <xsl:paramname="in_CanUseParams">yes</xsl:param>

    TO:

    <xsl:paramname="in_CanUseParams">no</xsl:param>.

  4. Stop and re-start the Analysis Services service. (By The Way, Congratulations. Your server is now in a totally unsupported state, so if you encounter a problem you’ll need to restore the unedited copy of the trdtv2r31.xsl file before contacting Microsoft Support for assistance).

  5. Start a Profiler trace to capture the Query Begin, Query End, and Progress Report End events. 

  6. Run the set of MDX queries that generated poorly performing parameterized SQL queries.

  7. Extract the SQL Statements in the Progress Report End EventClass and ExecuteSQL Event Subclass (EventClass =6 and EventSubclass=25)

  8. Send those SQL Statements to the Teradata DBA(s) so they can tune the AJIs.

  9. Stop the Analysis Services service

  10. Replace the modified trdtv2r31.xsl file with the original and unedited version of the file.

  11. Restart the Analysis Services service
    NOTE: You server is now back in a supported state

  12. Test the queries after the AJIs have been tuned.

 

After working through all of these challenges, we were ready to do some testing. Initial testing using a third party application directly against Teradata with no AJIs defined yielded average query response times of 00:02:05 to retrieve and render data. After building an OLAP database on top of Teradata and using Excel to
execute queries, again with no AJIs defined, average query response times were 00:02:03. The difference was not considered to be significant. After using the method describe above to identify parameter values and build a set of AJIs, with a cold Analysis Services data cache, query response times averaged 00:00:35
and with a warm Analysis Services data cache, query response times averaged 00:00:00.015.

 

Needless to say, we were able to successfully build the POC database in the span of a week and query performance actually exceeded customer expectations.