![]() Any other access, such as queries embedded in applications, will need to work with the normalized model. First, it only works for those users working with the specific tool. This does meet the usability requirement, but it usually has a price. Some BI tools have a metadata layer that can be used to display a normalized physical model as if it were a denormalized dimensional model. I’ll talk about type 2 dimensions to track the history in another post. Of course this is for type 1 dimension, where we don’t need to track the history of changes to the dimension. So any changes, like to the BrandName, will be captured in the daily rebuild. I usually do a complete rebuild of each dimension each day. ![]() The main difference between the two approaches is that the normalized version is easier to build if the source system is already normalized but the denormalized dimensional version is easier to use and will generally perform better for analytic queries. When you apply this across the 15 or 20 dimensions you might typically find associated with a Sales business process, the benefits are enormous. This 10 to 1 reduction in the number of tables the user (and optimizer) must deal with makes a big difference in usability and performance. In this simple example, the ten tables that contain the 12 product attributes are combined into a single table. Usability is significantly improved for BI application developers and ad-hoc users with the denormalized dimensional table. Note that the two models are equivalent from an analytic perspective. To save development time at the cost of decreased performance, instead of creating a new denormalized table in ETL, simply create a view in SQL Server or a named query in SSAS that performs all the necessary joins.įrom the single Product dimension table it is still possible to calculate SUM() by CategoryName, or by ProductColorGroupDescr, or any other attribute in the Product dimension, which includes all the product related attributes from the normalized model. In the dimensional version of the Product table, we would join the product-related tables from the figure above once, during the ETL process, to produce a single Product dimension table. The below figure shows the resulting Product dimension based on the tables and attributes from the above figure. From this set of tables, it’s possible to create an analytic calculation such as SUM() by CategoryName, or by ProductColorGroupDescr, or any other attribute in any of the normalized tables that describe the product. The base table is called Product and it connects to the Sales fact table at the individual product key level. The figure below shows an example of typical product-related attributes in a normalized model. Creating a single denormalized dimension table will result in a performance gain when processing a cube, as well as simplifying your star schema (avoiding having to use a snowflake schema). Building the dimension in the ETL system involves joining the various normalized description and hierarchy tables that populate the dimension attributes and writing the results into a single table. For dimension tables, you generally model these as one table per object. ![]() When building a SSAS cube, you typically have a star schema containing dimension tables and fact tables. ![]()
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |