Category Archives: Architect

Intelligent Cube – Part-1

Intelligent Cubes: An Intelligent Cube is a set of data that can be shared as a single in-memory copy, among many different reports created by multiple users. Rather than returning data from the data warehouse for a single report,
you can return sets of data from your data warehouse and save them directly to Intelligence Server memory. The reports accessing Intelligent Cubes can use all of the OLAP Services features for analysis and reporting purposes.

Intelligent Cubes are created and published for use as a shared data source for the users to build reports from. Intelligent Cubes provide the fast response time and analytic calculations that are often associated with Multidimensional Online Analytic Processing (MOLAP) cubes, while also benefiting from the ability to use Relational Online Analytic Processing (ROLAP) by drilling into the full set of data outside of the Intelligent Cube. In addition, Intelligent Cubes are fully scalable, limiting excessive data consumption and redundant data by allowing you to build only the sets of data you require.
An intelligent cube is made up of two files – an info file (contains the structure of the cube) and a data file. Each one of them has a representation in memory and indicates different things:

  1. When the data file is not  updated with its representation from memory, the cube’s “dirty status flag”  is set to true.
  2. When the info file is different from its representation in memory, then the cube’s “monitoring information dirty status flag” is set to true.

While (1) indicates that the data of the cube is incorrect (2) indicates that the cube’s monitoring information is different on disk than it is in memory.

The following section discusses an example of the workflow and status change for MicroStrategy 9.x Intelligent Cube:
P = Processing, A = Active,  L = Loaded, D = Dirty, F = Filed, M = Monitoring Information Dirty

Coming soon on dynamic cube usage

MicroStrategy Best Practises – part 1

First installment of best practices for MicroStrategy focusing on data warehouse. Thumb rule of best BI implementation,n not just MicroStrategy, is the optimized for setting up the data warehouse correctly. Here are few from me, few from MicroStrategy tech notes lost in deep. I shall post the best practices for MicroStrategy soon.

1. Data warehouse design should be snowflake schema.

2. Don’t give direct access to tables. Create view and provide access to them. Don’t create custom views.

3. Login/Password used for creation of DNS used to configure the MicroStrategy should be secret. DBA must not tell them to anyone and should be present to input L/P when MSTR Admin is configuring the I-Server.

4. Unique key of table should not be dependent on another other column of same table irrespective of such a column part of unique key to not.

5. Everyone of MicroStrategy team must have individual access to an interface to run SQL queries against the DEV WH with create access.

6. Everyone of MicroStrategy team must know the PK, FK and composite key of each table in OLTP (source) system.

7. All leading and training white spaces in table cells must be cleansed. Using SQL in MSTR (for same) is not a great idea. White spaces decrease SQL speed and it is visible for large warehouses.

8. Only fact tables should be partitioned.

9. To keep the ETL simple, you might want to build your re-normalized structure first, and afterward “generate” the higher level look-ups from the lowest-level look-up table.

Look forward for addition and criticism for above.

Late Edit: Every statement above has a blog post to justify. [:)]

Various types of Tables in MicroStrategy

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

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.