Architect

Various types of Tables in MicroStrategy

Aug 29th

Posted by Ashish Tiwari in Architect

No comments

Most of you would be working on it w/o knowledge. Pretty nifty.

  1. Base table: Provides source data for metrics. In a given SQL pass, the base table(s) may be fact tables or previously calculated intermediate tables.
  2. Lookup table: Provides attribute IDs and descriptions. A table functions as a lookup table if its columns appear in the SELECT, WHERE or GROUP BY clauses.
  3. Relationship table: Includes at least two attribute ID columns to join attributes. A lookup table can serve as a relationship table, or it could serve as both a lookup and a relationship in the same query.
    A table functions solely as a relationship table if it appears only in the FROM clause.
    A transformation table is a special case of relationship table.
  4. Partition mapping tables: MicroStrategy warehouse and metadata partition mapping use pre-queries to determine the physical tables to use for a particular metric. Warehouse partition mapping depends on physical tables in the warehouse, which will appear at the top of the FROM clause in the pre-query.

In Star schema, there is virtually no difference between Base Table and Lookup table.

Please let me know if there is any other type of MicroStrategy table.

Deleting unused Facts in MicroStrategy

Apr 30th

Posted by Ashish Tiwari in Administration

3 comments

I was searching a technote for a friend about distinct not apearing in the SQL (not something to do True Key, sadly) with the and landed up with this interesting technote which I’m going to implment for my current project sometime. We can’t use Object Manager to find this even though there is specific feature to do it as Tables are considered (logically) dependent on Facts. [Can anyone from MicroStrategy explain why is that? It should be other way round.]

Luckily it is simple but you need to have Select access on Metadata. Just run following query against your metadata database and your would get the list of afacts not used by any metric. Also, your would need to run it for each project your have. :(

SELECT object_id,
       object_name
FROM   dssmdobjinfo
WHERE  object_type = 13
       AND project_id = <Project ID> 
       AND object_id NOT IN (SELECT DISTINCT depn_objid AS object_id
                             FROM   dssmdobjdepn
                             WHERE  project_id =??
                                    AND depnobj_type = 13
                                    AND object_type = 4)
ORDER  BY object_id ASC

Disclaimer: Running a unmanaged SQL query against Metadata is not guranteed by MicroStrategy, Inc.

MSTR 9.0.1 (Release 2) and it’s New Features

Feb 5th

Posted by Senthil Raj in Administration

3 comments

MicroStrategy 9 Release 2 (9.0.1 products) is now available on the download site.

What are the new features in MSTR 9 R2 (9.0.1)

1) Jump Start Project

2)  MicroStrategy Health Center

1) JUMP-START PROJECT

                                    What is the Jump-Start Project?   

Jump-Start is a complete MicroStrategy project that comes with 7 dashboards and 30 reports and all the underlying metrics, filters, attributes, and dimensions.  All you have to do is add data and rewire.  It was designed with one purpose in mind – to help you develop a complete MicroStrategy Project FAST! 

                                     How does Jump-Start Work?  

MicroStrategy’s Jump-Start Project provides the standard building blocks of any project, attributes and facts, and uses those objects to create application objects such as metrics, filters, reports, enterprise documents, and dashboards.  Because a specific project has yet to be defined, all of these objects use generic names as placeholders.   You redefine/rewire the attributes and facts so that they work off your data tables, and due to the object oriented design of MicroStrategy, the data will be cascaded into the pre-built reports and dashboards automatically. With all this work prebuilt, the Jump-Start project delivers a complete BI project in a matter of a few hours

Manuals are available in MicroStrategy site.

2) MicroStrategy Health Center

MicroStrategy Health Center allows users to prevent, diagnose and fix problems in a MicroStrategy system.  It detects known problems and provides an immediate solution to many of them.  

Health Center can email a system administrator if it encounters a problem with the system. In cases where Health Center cannot fix a problem immediately, it enables users to bundle relevant system information, such as log files, into a diagnostic package and transmit the package to MicroStrategy Technical Support for review and troubleshooting. 

  • MicroStrategy Health Center can only operate on MicroStrategy 9.0.1.
  • By default, MicroStrategy Health Center is installed and turned off (not configured). To start configuring MicroStrategy Health Center, please see the tech note below:
    • TN32554 How to Configure a MicroStrategy Health Center System
  • If MicroStrategy Health Center is already configured, to learn more about what common tasks can be carried out, please see the tech note below:
    • TN32552 How to use MicroStrategy Health Center Console
  • For a List a Current System Checks:
    • TN32551  A List of System Checks for MicroStrategy Health Center
  • MicroStrategy is constantly extending the functionality of Health Center by developing new system checks and information collectors, and refining existing ones. To keep the Health Center System up to date:
    • TN32553 How to keep the Health Center System up to date

Source: MicroStrategy’s Technical Email

“Outer Join” in MicroStrategy

Jan 13th

Posted by Slavik Taubkin in Architect

No comments

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.

Solution for not able to add tables in Warehouse Catalog

Dec 18th

Posted by Senthil Raj in Architect

No comments

Issue: Not able to add tables/views to the Schema in Warehouse Catalog

Error: ‘Error adding Table(s) to the schema. This key is already associated with an element of this collection’

Solution in MSTR 9:

1) Open ‘Architect’,

2) Right click the table <table name>

3) Add to the project

4) once its added, Right table in Left Pane and update structure

5) Save and close (it will ask for Update Schema, so do the same

6) Now open Warehouse Catalog

7) you can see the table/view added to schema

1234»