Microstrategy Blog

According to MicroStrategy training:

“A measurable value, often numeric and typically aggregatable, stored in a data warehouse.

A schema object representing a column in a data warehouse table and containing basic or aggregated numbers-usually prices, or sales in dollars, or inventory quantities in counts.”

How…technical.

Let’s explain it in terms the terms of an “every day life” transaction. If you bought shoes at the shoe store, the transaction involved a certain amount of “facts” and “attributes”. The facts in this case are “dollars, and quantity”. You gave something measurable, to get something measurable. Your fact is the base level of the values in the transaction that we later turn into metrics. The attributes in the transaction are almost limitless: the Converse All Stars, the shoe size, the store name, the person who you bought the shoes from, the time, day, and month that you bought them in. Metric are then built around facts. The metrics would be total dollars spent, average sales price, or the number of sales.

Simple enough.

The fact SALES_AMOUNT has been enjoying a quiet life for the last five years until, one rainy day, the new QA guy called Eureka! and proudly informed everyone that according to the naming conventions the fact name should be SLS_AMT. Of course, he took the liberty of checking the source tables and discovered that they too should be updated to match the foresaid conventions. Way to go, QA guy!

What do you do? You could swear at the QA guy or, if you are more literary inclined you could write him a bitter limerick and post it on the Intranet…

Seriously now, how do you go about accommodating this change? I’ll skip the whole ETL part and go directly to MicroStrategy, right after the source tables were updated.

What you can’t do is go to the Warehouse Catalog and update the source tables’ definition. The catalog would just throw a number of dependencies that need to be solved before performing the update. You can’t delete the fact either, as it is being used by several metrics which in turn are being used in reports, and so forth.

The thing to do is to make the fact independent of any table. There are some ways this can be achieved, and one of them is to clear the fact definition and map it manually to “1”. You can actually write anything in the definition field, as long as it is not “SALES_AMOUNT”.

Thus the fact is no longer related to any of its source tables. Now it’s the time to update the tables’ definition in the Warehouse Catalog.

After that, just go back to the fact editor and map it automatically to “SLS_AMT” and perform a schema update.

A similar process can be applied to attributes. Rather than mapping an attribute element to “1”, you can try to temporarily map it to any other column in any other table. Most likely the relationships with other attributes will be lost because of this, but they can easily be restored when all work is finished.