Category Archives: Schema

Solution for not able to add tables in Warehouse Catalog

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

Version control in MicroStrategy

MicroStrategy doesn’t support Versioning. BTW, There are two types of versioning in MicroStrategy. Source control (what I’m taking about over here) and Slowly changing dimension (SCD). Lots of clients have put requests to MicroStrategy to have this feature of version control for schema object or at least attributes. In short the answer for this question is NO and I would never want MicroStrategy to have this features. Such a feature request is only idea that can come from bad MicroStrategy practice. Why in this world, do you want to have two definition. If a change in attribute breaks a report or sql goes haywire, one should rather fix this (that why it is job of MicroStrategy Architect). Such a feature would lead to lots of useless trial-n-error stuff in MicroStrategy. If you know any company who is behind this feature request, please post the name over here.

FYI, as per document ID (TN4100-800-0349), A current enhancement request exists for this functionality. Contact MicroStrategy Technical Support for the latest update of this request.

This would be a good feature for nuts in MicroStrategy and will give sane people hard time.

Update 13 Feb 2009

Cognos supports this feature. But one can’t use different object(s) from different set of version. :-(

What’s factless fact

I have been reading about factless fact table for quite some time and had assumption that bridge table in case of M-M relationship was “the” factless. But while having a knowledge sharing session I was given a total new definition. That was convincing but unbelievable. Though bridge table is actually a factless fact table but not as per Kimball.

A factless fact table is table that doesn’t have fact at all. They may consist of nothing but keys. There are tow types of factless fact table. 1-> event 2-> coverage.

Take an example of a factless fact table that records an event. Many event-tracking tables in dimensional data warehouses turn out to be factless. Take an example of tracking student attendance. Imagine that you have a modern student tracking system that detects each student attendance event each day. When the student walks through the door into the lecture, a record is generated.

One can easily list the dimensions surrounding the student attendance event.
Date: one record in this dimension for each day on the calendar
Student: one record in this dimension for each student
Course: one record in this dimension for each course taught each semester
Teacher: one record in this dimension for each teacher
Facility: one record in this dimension for each room, laboratory, or athletic field

The only problem is that there is no obvious fact to record each time a student attends a lecture or suits up for physical education. Tangible facts such as the grade for the course don’t belong in this fact table. This fact table represents the student attendance process, not the semester grading process or even the midterm exam process. Actually, this fact table consisting only of keys is a perfectly good fact table and probably ought to be left as is

A second kind of factless fact table is called a coverage table. Coverage tables are frequently needed when a primary fact table in a dimensional data warehouse is sparse. Take simple sales fact table that records the sales of products in stores on particular days under each promotion condition. The sales fact table does answer many interesting questions but cannot answer questions about things that didn’t happen. For instance, it cannot answer the question, “Which products were on promotion that didn’t sell?” because it contains only the records of products that did sell. The coverage table comes to the rescue. A record is placed in the coverage table for each product in each store that is on promotion in each time period. In general, which products are on promotion varies by all of the dimensions of product, store, promotion, and time. This complex many-to-many relationship must be expressed as a fact table.

The coverage table must only contain the items on promotion; the items not on promotion that also did not sell can be left out. Also, it is likely for administrative reasons that the assignment of products to promotions takes place periodically, rather than every day. Often a store manager will set up promotions in a store once each week. Thus we don’t need a record for every product every day. One record per product per promotion per store each week will do.

Answering the question, “Which products were on promotion that did not sell?” requires a two-step application. First, consult the coverage table for the list of products on promotion on that day in that store. Second, consult the sales table for the list of products that did sell. The desired answer is the set difference between these two lists of products.