I'm kind of new to dimensional modeling. It seems to me that a lot of my data do not have specific measures. For instance, we have Organizations, and each has 1 or more Locations. There are attributes at the Organization and at the Location level that we need to filter/slice by. The only real measurement we care about is the count of Organizations and Locations, e.g., how many Organizations exist in a certain zip code.
- Do I create one fact table where each Location shows up once, and Organizations show up 1 or more times? Then I have to deal with duplicate Organizations.
- Do I create two fact tables? If I do, do I create a bridge table between them?
I'm leaning towards #2. Am I right?
If you have facts at the location level of granularity then you would normally create a Location dimension that included the parent Org and its attributes (i.e. you denormalise the Org into the Location). You might also have a separate Org dimension if you have facts at the Org level as well.
If you can answer a query by just using a dimension table then there is no requirement to build a fact table e.g. if you want to know how many Locations per Org you have then just query the Location dimension table.
Obviously if, for example, you are using a BI tool that requires a fact table then you may need to create one in your DWH if your BI tool cannot construct a "virtual" fact table from the Dimension