How to set MicroStrategy to use SQL Server's minimally logged INSERT for the intermediate temporary tables

By -
This is going to be a discussion around some specific VLDB settings which once enabled, can provide flexible control over how the temporary tables are used in fulfilling a report request. As you've probably noticed by now, for some types of data merging (usually when a  report has metrics coming from separate fact tables), MicroStrategy uses multiple passes to fulfil it. Those intermediate passes will then be merged in a last pass (if metric join type is set to outer then the last pass is a full outer join). In the case of SQL Server database instance, the intermediate passes will store the data into true temporary tables, as stated by the "Tables -> Intermediate Table Type" VLDB option.

During the report execution, the temporary tables are created under the tempdb database using SELECT INTO statement. This is the fastest way to create and populate a table in SQL Server, mainly because it's not a logged operation like INSERT and DELETE.

We're constantly looking for ways to improve performance, so we soon discovered the parallel execution of the report passes. This is controlled by the project level VLDB option "Query Optimizations -> Parallel Query Execution". This option helped us with costly reports where multiple fact tables metrics are merged into the same report. The advantage is that MSTR will execute some of the passes in parallel, reducing the overall time necessary to fulfill the report.

But there's a catch. Enabling this, had an impact on how the temporary tables behave and for a good reason. As the temporary tables are generated in parallel, on separate SPIDs, they need to be visible between connections so that they can be merged in the last pass. That's why, after enabling this VLDB option, the temporary tables are materialized on the default database setup in the ODBC DSN. This does come with an additional headache, as "SELECT INTO" puts an exclusive lock on the systems tables, blocking other processes (ETL and other MSTR reports). The lock can stay there for a while, from seconds to minutes, depending on how heavy those report passes are.

In order to take advantage of all the VLDB setup already performed, we had to find a way to prevent MSTR processes from blocking each other or the ETL. The only viable option is to use an operation that would not require system tables locking and that is an INSERT. By default, SQL Server performs a logged insert which is slower than a SELECT INTO, but in certain conditions a minimally-logged INSERT (MLI) operation can be performed which reduces a lot the performance gap. For the requirements of MLI, checkout this link.

This being said, we identified a set of VLDB options to achieve the minimally-logged insert:
  1. First step is to switch from SELECT INTO to INSERT INTO and the VLDB setting for that is "Tables -> Table Creation Type -> Explicit Table"
  2. In the second step we have to force a table hint "WITH (TABLOCK)" at the insert statement level as per MLI requirement using "Tables/Insert -> Insert Table Option"

After the 2 VLDB changes, MSTR will create the temporary tables through a DDL statement (CREATE TABLE) using the definition of the attribute alias and then perform an INSERT INTO operation with the hint we predefined.

It's true that we do not have the same level of performance offered by SELECT INTO, but eliminating the locks using INSERT INTO, balanced the execution of the reports, so the overall user experience has been improved.

Comments

  1. UnoGeeksJune 2, 2022 at 5:54 AM

    Thank you for sharing such a nice and interesting blog and really very helpful article. Mulesoft is the Most Widely Used Integration Platform. If you want to become Mulesoft Certified Developer, attend this Best Mulesoft Training Course offered by the Unogeeks (Top Mulesoft Training Institute)

    ReplyDelete

Post a Comment

Popular posts from this blog

The count distinct adventure - Part I

By -
Image
I've been working with several reporting and data analysis tools for a while now. Started with SSAS cubes, then Business Objects came along, later, MicroStrategy (MSTR) joined the team and then Tableau. They all have their own ways of managing count distinct (CD), but they yet have more in common. Manage simply means yielding the good results every time a user interacts with that CD measure. Us, the BI tech guys, we have a goal to facilitate easy data analysis to the users, especially that most of them work in non-IT domains and have different skill levels. Throughout time, CDs have proved to be a difficult thing to manage and if it wasn't the ease of implementation, it was the performance impact we soon discovered. It's been known that the difficulty lies in the nature of this non-additive measure. Basically, we cannot sum it as a standard revenue measure, but you always have to re-calculate it when the level of the aggregation changes. Have to admit that I like a goo...
Read more

The surrogate key strikes back

By -
If you ever participated in building a data warehouse or worked around data warehouses, you've probably came across the surrogate key (SK) notion. If you’re not familiar, then take a look at this article on the Kimball's group website. Surrogate keys are very common in data warehouses and they bring a lot of functionality, from design to performance enhancements. Now, one of the surrogate key's drawbacks is the difficulty to keep it in synch between all environments. As each environment can be linked to a different version of the source (e.g. UAT, live environments etc.), surrogate keys can get off for the same natural key and this can also have an effect in MicroStrategy. When building attributes you have to define its forms. The ID form should contain a unique key describing the attribute in order to provide correct joining, aggregation and attribute member list when applying filters. Indeed the surrogate key doesn’t have any business meaning, so this form w...
Read more

The count distinct adventure - Part II

By -
During the first part of the topic we introduced the challenge of count distinct and 2 solutions to address them, along with their advantages and disadvantages. The list of solutions continues in this final section and these, presented below, are also the solutions that we use across our projects where CDs are required. Solution 3 - Count Distinct derived metrics This functionality is probably the most known by the users and mostly used, as it offers flexibility and the necessary control without the intervention of the IT teams. Creating =derived metrics in reports, Vis and documents is a walk In the park, using the context menus. With the deployment of V10, things got even more flexible adding support for conditional derived metrics (IF and CASE) by using attributes directly. As you probably remember, or still use today, up until V10, building conditional derived metrics was a hustle, as only metrics could be used with IF and CASE statements. You would first create a MAX(<...
Read more