While it can be a wide reaching topic, there are some good high-level data warehouse concepts to keep in mind. Depending on the BI vendor and application, they can appear with different names than what is stated here.
Kimball methodology – One of the leaders and originators of the datawarehouse concepts and theories. States that the datawarehouse is the center of the BI work.
Inmon methodology – States that the DW is one component of the data paradigm, with smaller data marts also being used as sources off the main source.
Dimensions – this is a category of data, typically things like time, stores, geography, etc..
Attribute – One of the dimension “levels”, like Month, State, etc…
Fact table – This would be data of end investigation, which is usually things like sales, revenues, items sold etc..
Lookup table – This has data about the dimensions and typically has some sort of ID to relate to the fact table. Depending on the number of attributes in a dimension ,there may be several levels of lookup tables.
Granularity- the lowest level of the dimension that needs to be worked on. For example, day vs. week. Having a lower granularity will mean more data, but more detailed reports.
Star schema - This is a datawarehouse design where there are only one lookup table per dimension on a fact table. It’s the simplest way and a common starting point for a basic datawarehouse. This typically has the fewest amount of tables and is considered denormalized.
Snowflake schema –This will have multiple lookup tables, usually one for each attribute needed. This will have more tables but typically allow for more complex queries. This type of schema is considered normalized.
Normalized database – Data where larger tables are broken up into smaller dimension tables.
Denormalized data- Data where larger tables are used for lookups.
Copyright 2011-2019 BusinessIntelligenceBase.com