Fact dimensionality
I often run into the situation the we have several fact tables with different grains, but are related through a single ID at the transaction level. Typically the dimensionality is the same, but the related information doesn't exist in the source

Multiple Fact Tables vs. Consolidated Fact Table
I am trying to put together the bus matrix (to identify the facts tables) for a government correctional agency and am debating between multiple facts for each event in the life cycle of an offender vs. fewer fact tables. I wanted to avoid joining multiple

DATE OR DATE KEYS IN FACT TABLES
I've read different opinions about the usage of dates in fact tables. If a date is NEVER used to filter and only reported, what is the purpose of adding it as a date key instead of a plain date type? Aren't we wasting the resources to get to the date

How do I connect fact tables for drill down
I need some advice on a project I am modeling for a Hospital. We have 2 business process' that I am creating individual fact tables. The big questions is - how do I connect related Fact Tables for drill down ? Details: Reimbursements - the

Using a dimension in multiple fact tables with different grain and support SCD
Hello users, i've a question about using a dimension in fact tables with different granularity and at the same time retain support for SCD. My scenario: I have one Dimension called Apartment. This Dimension cosists of the following related

Snapshot Dimension & Fact tables
We are currently designing a logical multidimensional model from an OLTP tables.Dimension tables have monthly snapshot because some of or all the attributes might change on monthly basis.The same situation for the fact table has monthly snapshot. I kno

Does it belong in the stage tables or fact tables?
If I have a fact table with a "counter" fact, for example a customer places an order, but then goes back and changes parts of that order any number of times, I want to store how many times the customer changed his/her order. The grain of the

Fact table structure
I am pretty knew to the matter and need some guidance to the below. I have been of the view that facts shouldn't contain any descriptive information. But, pretty recently i switched companies and found that we have a scenario where facts in the area had

Modelling question - Multiple fact tables with one to many relationships and querying from many fact tables
Its a new capital markets data warehouse in design stage. I have deal_deatils_fact - 1 row per deal with all measures at deal level - dealID (Degenerative DIemsnion - DD) deal_tranche_fact - 1 row per deal per tranche with all measures at the

Regarding Factless Fact Tables
Some time ago I was asked to build the BI platform for a road safety organization. The warehouse was built of three primary fact tables, each with a collection of associated dimensions. I am now trying to branch out into ETL and Warehouse design, and from

A fact table surrounded by a lot of references tables
I am reviewing a design which is about to be implemented after build. There is a fact table with 17(!!) foreign keys. 14 of these keys are two-letter codes pointing to reference-tables (as we call them). A reference table consist of codes with

Fact table type
Basic questions about fact table type in kimball methodologies. Would appreciate if someone could explain 1)Transactional fact table type This is the transaction event captured from source OLTP system. Fact table in this case has all the keys instead

Building aggregate fact tables from staging
Hi, Is it acceptable to create an aggregate fact table from the staging tables after you have cleaned out the data in staging? I know usually you're supposed to create aggregate fact tables from the fact/dimension tables, however can it be done straigh

Fact Table Grain at a Sub-Atomic Level
I'm thumbing through the Design Tips and would love to find some thought given to the statement made in The Data Warehouse Toolkit: The Complete Guide to Dimensional Modeling (pg 34): "Preferably you should develop dimensional models for the most

Multiple measures in a fact table- modelling question
Hi all, First, sorry for what may be the super easy question for you... There are around 100 different metrics with the same grain to be analyzed against the same set of dimensions. The initial idea was to build one big table ~100columns wide with a