Posts

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

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 constan...

8 reasons why you should upgrade to MicroStrategy 10

Flash is dead As you probably may know, Flash is no longer the favorite technology to build the web and it's losing ground more and more to the point where Google no longer wants to support it.  MicroStrategy made the necessary changes and moved everything to HTML5 and JS. This also increases the compatibility across platforms/devices, making deployment a lot easier. Redesigned Visual Insight There's no doubt that Dashboard/VI is the de facto standard in MicroStrategy analytics. VI is also the main vessel for data discovery and self-service BI. Documents are old flavored, non-responsive which take long time to build. Some would also argue that documents will be deprecated in a future release of MicroStrategy. Easier upgrades MicroStrategy has made things easier for deployments and upgrades, allowing installations over an older version of the platform without removing it first. This reduces considerably the necessary time to deploy a new version. Moreove...

List of available auto-text macros in Report Services Documents

This is a list of the available auto text macros that the Report Services Document engine recognizes.  AUTOTEXT DESCRIPTION   {&PAGE}  Display the current page.  {&NPAGES}  Display the total number of pages.  {&DATETIME}  Display the current date and time.  {&USER}  Display the user name that is executing the Report Services Document.  {&DOCUMENT}  Display the document name.  {&DOCUMENTID}  Display the document ID.  {&DESCRIPTION}  Display the document description.  {&PROJECT}  Display the project name.  {&EXECUTIONTIME}  Display the execution time.  {&SERVERNAME}  Display the Intelligence Server name  {& REPORT :FILTERDETAILS}  Display the filter details of that report.  {& REPORT :PROMPTDETAILS}  Display the prompt details of that report.  {& REPORT :REPORTDETAILS}  Di...

The surrogate key strikes back

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...

The count distinct adventure - Part II

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(<...

The count distinct adventure - Part I

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...

Derived Attribute is the New Custom Group

Image
There’s always delicate balance, when developing a MicroStrategy Schema, on when to know to build an auxiliary object in MicroStrategy and when to push that work to the ETL/Database. Most of us must have faced situation when there is need of virtual attribute and Database developers are busy to provide attributes in desired format directly in database or business requirements are different for one particular user or group. In this situations we generally opt for Custom groups which impacts performance drastically. Ø   Each Custom Group element is a separate filter and generally creates a separate pass of SQL. Ø   Multiple Custom Groups on a report end up multiplying by each other in regards to the number of passes of SQL.  So if you have a Custom Group with 10 elements, and a 2nd Custom Group with 10 elements, that’s 100 passes of SQL times however many passes the Metrics on the report create. With 10 version of MicroStrategy, we can create derived a...