All posts by Slavik Taubkin

About Slavik Taubkin

Slavik is an IT consultant currently working for Teradata in Germany. He come originally from the Ukraine’s second largest city Kharkov and live now with his family in a small city near Frankfurt.

BusinessObjects vs. MicroStrategy

On the MicroStrategy-website white paper can be downloaded, tells where in 10 pages why Business Objects is beaten in all possible fields of MicroStrategy. As my readers, but certainly the decision not want to leave a competitor and is also intended for the subtle but important details of interest, I have made the following (incomplete course) to compare the features of MicroStrategy and Business Objects.

As mentioned before, I have considered only the features relevant to the SQL generation of meaning. I have also considered that MicroStrategy and Business Objects to build on fundamentally different approaches and some features are therefore not simply comparable. Such radio ions so I left aside.

The plus sign in the table means is that each tool has an important feature that for the other product would be implementable, although certainly, but unfortunately is not available.

Some “little things” that are in my view, just very important and therefore be present at every BI tool should not be missing, but in the current release of Business Objects. This is why I have marked with a minus sign.

And now the scoreboard:

Feature MicroStrategy Business Object
1 Automated Aggregate Awareness +
2 Into account the cardinality in SQL generation (eg Subquery with an M: N relationship) +
3 Dimensional metrics and conditionals +
4 Filtering using HAVING (QUALIFY is missing even in Micro Strategy) +
5 Consolidations; Dynamic Groups +
6 Assembly of the key figures within the database +
7 Joins on operators other than “=” (eg BETWEEN Join for Slowly Changing Dimensions) +
8 A simple way to choose one of the join paths (via contexts) +
9 No examination of the object dependencies when deleting
10 No SQL-Preview for the prompt reply but before the execution

MicroStrategy has over so much more powerful means for the rapid generation of high-performance SQL code – and that’s without taking account of VLDB countless settings that exist for the fine-tuning of the SQL query for MicroStrategy!

An important advantage of Business Objects is in my view, only the possibility of any conditions in the join between the tables to use shortcuts.

Based on Google Translator of original article.

No SQL-Preview for the prompt reply but before the execution

MicroStrategy Symposium in Dusseldorf (October 2008)

Yesterday I gave a talk at the MicroStrategy Symposium in Dusseldorf. The theme of my presentation was “High-performance reporting with MicroStrategy and Teradata.”

Since I’ve caught a cold last weekend pretty much, was my biggest worry is that I will interrupt my speech every 5 minutes by uncontrollable coughing fits. EinPaar against dry cough lozenges, however, have worked wonderfully and I could entertain my audience for nearly an hour long carefree.

After I’ve heard some even had hundreds of speeches in my life, I wanted to bore my audience in any case with the superficial marketing films.   I simply could not allow that they will be too technical, because I did not sleep the total of the coveted audience wanted to risk it.

I assumed that my audience will have relatively good knowledge of MicroStrategy: otherwise they would not participate in the MicroStrategy Symposium. The Teradata information could however be rather rare so far. For this reason I have my old “MicroStrategy and Teradata” presentations that I prepared for Teradata customers can use without further: they provided the functionality of MicroStrategy in the forefront and dealt with the concepts of Teradata only marginally. I have therefore one new lecture prepared, which has included, inter alia, a 5-slide introduction to the Teradata architecture.

Minus: Some “human losses” during the lecture.
Plus: positive feedback after the lecture.

If you, dear reader, have heard my talk, write me please, what out of your sight was good and what needs improvement.

Original article is here. Translated using Google Translator.

“Outer Join” in MicroStrategy

During the past few weeks, I was confronted with many interesting tasks of the MicroStrategy world. And once again I had to annoy me about the fact that there is no clever way to MicroStrategy, an outer join between the attributes to be generated.

In Menu Data / Data Options / Calculations / attribute – join method can be adjusted, although, as the connection is to be made between the attributes, but these settings will lead to a difficult to understand SQL, the unexpected also depending on the situation to a conclusion can lead (more) in one of the next post.

In addition to the modification of views, there is another common workaround, which is to create a dummy metric, which is defined for each combination of attributes.  This metric will be hidden in the OLAP area, or via formatting in the report. Example of such a metric would be a simple constant function Sum (0).

Such a dummy metrics produces the same result as if all the attributes of the template would be linked to a FULL OUTER JOIN.

But what if you want some attributes with a FULL OUTER JOIN, and then connect the other with LEFT OUTER JOIN?  A simple idea would be to modify the metric by means of dimensionality, namely, the LEFT OUTER JOIN attributes with the setting ignore add / None.

Great idea, but does not work!  From a no apparent reason, no metric dimensioned in MicroStrategy can be “conducting”, ie standing in a LEFT OUTER JOIN on the left side. Too bad!

The original article in German language can be read here.

Using a transformation to calculate 5 last values within one SQL query

Here is one of my recent interesting tasks:

Calculate some metrics only for the items that are in high demand. An item is “in high demand”, if its sales value were greater than the constant X in every of the last 5 months (including the current month).

Solution

First again setting of tasks:

An article A is valid in the month M as an article with large demand, if its Abverkäufe in everyone of the last 5 months (inclusive the current month) were larger a fixed constant X (e.g. was X = 100). We want to find and in a report represent as efficiently as possible all to such combination A and M (which we if necessary than a Metric filter to use later to be able).

Here my solution is using only one transformation. I see the advantage of this solution in the fact that only once the generally very large fact table one accesses.

For all examples I will use according to standard installed the project in my blog MicroStrategy Tutorial. Therefore you can my examples easily copy and try out. In addition it is possible to compare several solutions of a problem directly.

As I already betrayed, I use a transformation in my solution. Therefore a Transformations view must be defined as the first. The data source for the project „MicroStrategy Tutorial “is an ACCESS data base TUTORIAL_DATA_7200.mdb.

We put on the following View:

SELECT t1.MONTH_ID, t2.MONTH_ID AS PREV_MONTH_ID, DateDiff („m “, t2.MONTH_DATE, t1.MONTH_DATE) AS MONTH_DIFF
FROM LU_MONTH AS T1, LU_MONTH AS t2
WHERE (((DateDiff („m “, t2.MONTH_DATE, t1.MONTH_DATE)) Between 0 and 4))
ORDER BY t1.MONTH_ID, t2.MONTH_ID;

The VB-function DateDiff supplies here the number of full months, which lie between the handed over date values. The expenditure of the View looks then about in such a way:

Image 1

We designate the View „MONTH_TO_5_LAST_MONTHS “. Now the View in the Warehouse catalog is imported; subsequently, on the new logical table the following objects are defined:

Transformation “5 load Months “

Image 2

Attribute “Months Diff “

Month Diff

Month Diff

In addition the attribute form Month@ID should be extended automatically to the field MONTH_ID the new table:

Month@ID

Now we want to build a Metrik, which „a bit-map with 5 digits represents “to the form *****. Each asterisk corresponds thereby one month: the linkste asterisk refers to the month in the regarded report line, the next asterisk to the previous month (relative to the regarded report line), etc.

In place of each asterisk we want to indicate the indication „to X “, if the Abverkaufswert of the article were larger in the regarded report line in that asterisk position corresponding month 100. Otherwise we indicate in place of the asterisk the indication to O.

If thus for an article in the month the bit-map is indicated to 200804 „OXXOO , that means that this article had the Abverkaufswerte only in the months 200803 and 200802 more largely 100. According to the definition from setting of tasks such an article would not have large demand in the month 200804. “Only the combinations of the articles and months correspond to the large demand, for which the bit-map is calculated.

And here the definition of the Metriks is bit-map Sales > 100 in The load 5 Months:

Metric condition

The dimensionality and Konditionalität of the Metrik are not changed. Only the transformation „5 load Months “is assigned the Metrik.

The formula of the Metrik looks as follows:

ApplyAgg(„max(iif((#0 = 0 and #1 > 100), ‘X’, ‘O’)) & max(iif((#0 = 1 and #1 > 100), ‘X’, ‘O’)) &max(iif((#0 = 2 and #1 > 100), ‘X’, ‘O’)) & max(iif((#0 = 3 and #1 > 100), ‘X’, ‘O’)) & max(iif((#0 = 4 and #1 > 100), ‘X’, ‘O’))“; [Months Diff]@ID; [Units Sold])

Because of the function ApplyAgg one must enter the logic in VisualBasic syntax (iif for the konditionale examination and & for the Konkatenierung). Probably it would function however also without ApplyAgg function, with the MicroStrategy functions IF and Concat. Logical way should generate however MicroStrategy in this case a very similar Query.

Here is screen SHOT of a report, which uses the Metric bit-map Sales > 100 in The load 5 Months :

Final Report

The original article in German language can be read here.

MicroStrategy Object Exchanger

MicroStrategy Object Exchanger is a custom written tool that allows automatic and fast replacement of all occurrences of an attribute or a metric in all available reports. The formatting of the replaced object will be preserved!

Compared to the conventional manual way (open each report, replace the object, format the object again, save the report) Object Exchanger helps you to save many hours of hard and error-prone work.

MicroStrategy Object Exchange

MicroStrategy Object Exchange

The tool is based on MicroStrategy Intelligence Server API. You will need .NET Framework Redistributable (from Version 2.0) to start the tool. You can download it for free from Microsoft.

I used this tool at one of the German retail customers to replace one of the attributes (Item) with another one (Item Group) — but in over 100 reports! The whole process took only about 2 minutes.

Feel free to use MicroStrategy Object Exchanger in your projects. You can download it here. Just unpack the ZIP-file. The interface is very intuitive. Please let me know about your experience using this tool.

Note: You must have MicroStrategy installed on the machine where you want to execute this tool.

The Object Exchanger currently only supports the metadata from 8.x versions. To be on the safe side don’t forget to backup your metadata! I look forward for your comment, views on this tool.

You can read more about MicroStrategy Object Exchanger at my blog in German language.

Application(s) is provided “as is” and MicroStrategy101 and individual application author(s) disclaim all warranties, whether express or implied, with respect to all provided items, including all warranties of merchantability, fitness for a particular purpose, accuracy, systems integration, title, satisfactory quality and non-infringement. MicroStrategy101 and individual application author(s) do not warrant that use of the provided items or any portion thereof will be uninterrupted or error-free, or that the provided items or any portion thereof will meet any need or requirement of licensee.