There is another important difference between accessing a fact table and a lookup table. If a value, such as April sales, is missing from a fact table, the row still exists in the table and is reported as null or zero. If that same value is missing in a lookup table, the April row does not exist. The previous or next value (March or May) is reported, depending on whether the level is set to beginning or ending value.
—
Please post your comment on this.
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.
During my days at Cybage, I used to read lots of internal documentation from MicroStrategy. Well, I had access to or were give documents for knowledge purpose. For months, even after having left Cybage, I used to try to decode why MicroStrategy likes Snowflake schema more than Star Schema. (Kimball hates Snowflake and abuses it like anything, read Warehouse Toolkit by him). That’s why I used to like reading about Enmon more. I came to know about this so call affinity was actually a shortcoming the way MicroStrategy was designed and/or programmed.
MicroStrategy document used to say and recommend Snowflake over Star. Reason – MicroStrategy is designed to make most of snowflake schema. Well actually it is a bug in MicroStrategy that will prevent it to work perfectly in Star schema.
Snapshots are from MicroStrategy Tech Notes:
This schema is characterized by one lookup table per dimension, with base tables at the lowest level. This is the fastest way to set up a data warehouse:
This type of schemas is fully supported but difficulties may arise when adding aggregate tables:
Problem —-> Double counting
According to the diagram above, a report that contains [Month] and the a metric SUM(SALES_AMT) will go to the aggregate table [MONTH_STORE_SALES] and join to the [MONTH_ID] column to retrieve the description from the [LU_TIME] table. Since the [MONTH_ID] column is not unique in its lookup table, the results will appear duplicated.
Why —-> MicroStrategy is optimized to work with snowflake schemas, where each attribute level has a distinct lookup table.
Solution —-> If aggregate tables are needed, use one lookup table per attribute to avoid double counting.
Reaction —–> Give me a break.
I could never understand why this affinity. I came to know about this during my early months working on MicroStrategy and no one could solve it.
BTW, I was lucky to know this thing. Person holding position of Director had come to India for knowledge transfer. He is the man behind getting MicroStrategy into the company.I had gone for a tea break and when I came back I didn’t notice that every MicroStrategy developer is missing. I thought they must have gone for a break. But I thought checking out what’ was going on. Karthik told me there was a session by Asif but he said it won’t help me much. Still, I thought I should attend and in that meeting I came to know this. Wow.. this made the visit fruitful for me.
Status of this defect —–> It has been weeded out. I’ve done several implementation of it. But working on 7.2.2 was pain. I had to go for duplicate Logical tables. Those days it was not a good help too.
—-
Update on 21st Sept 2008
MicroStrategy have updated technote last month stating that custom logical tables are workaround to solve this problem.
After a long long time, finally, my stock portfolio doesn’t have anything in red. Spice Jet, Ashok Layland and Empee have always given me jitters. Specially, Spice Jet, which was always in the red the day I bought. BTW, I made windfall gain in Piramyd Retail. 120% in 10 days, thanks to Edelweiss Capital Limited. Even though the stock gained 5% next day of sale, I don’t regret. I can’t believe such a huge gain in such a short span of time. Kolte Patil has come out to be a bad IPO. Just 24% gain on listing day.
My father has just learnt that I have started investing in stocks. He happen to having gone to J B Nagar home to collect mails and he saw a refund challan of Rs 81200. Book running lead manager send the proof of refund to the registered address. He hadn’t said anything but I know the day I lose money in stock, I’d be hearing a lot from him. “You know stock market. It is gambling. Those who don’t do anything invest in stock market. Are you a crorepati? Only big shots put money in stocks?” I can imagine all that.
This is the most common question that I have faced after the metric options. I have a simple answer to it, in my own way. “It is modern age custom wisdom”.