The tools use metadata-definitions about the dimensional snowflake model-in order to generate reports and queries. Some BI tools are built specifically to leverage snowflake schemas. If the number of levels in the hierarchy is relatively constant, developers prefer this type of model, because it’s closer to the ER model used in the source systems. This approach is most often used with dimensions that have a very large number of rows with deep hierarchies that are relatively static.
![snowflake bi tools snowflake bi tools](https://icedq-abyssgroupinc.netdna-ssl.com/wp-content/uploads/2020/07/DevOps-approach-to-Snowflake-migration-testing-iCEDQ.jpg)
Summarizing by product data for internet sales would require joining the sales fact table to each level of the product hierarchy until the highest level required for the summarization is reached. In this example, the product dimension in a star schema is split into three tables representing the three levels of the hierarchy-product, product subcategory, and product category in a snowflake schema. For the bike store, the top-level categories might be bikes, clothing, and accessories. ĭimProductCategory, the highest level of the hierarchy.For the bike store, the bike-related subcategories might be road, mountain, and hybrid. ĭimProductSubcategory, the next level, which rolls out of various products into subcategories.If this was a bike store this might be the specific bike, biking shorts, or water bottle sold. With respect to the keys of fact and dimension tables of snowflake schemas, they are also filled with surrogate key values, just like the keys in star schemas.ĭimProduct, which is the core or bottom level of the hierarchy. For the query, in order to get the URL of a website title, only a very small table has to be queried. In addition, a snowflake schema can support queries on the dimension tables on a lower granularity level. The advantage of a snowflake schema is that less duplicate data is stored than in an equivalent star schema.
![snowflake bi tools snowflake bi tools](https://lyftron.com/wp-content/uploads/2020/04/datasourcing.png)
![snowflake bi tools snowflake bi tools](https://static.wixstatic.com/media/6e15cb_f72ec1c737f448d9aad251e112958d76~mv2.jpg)
In fact, in both snowflake schemas and star schemas, no many-to-many relationships are used. In other words, the data in the REGION table has a lower granularity level than that of the CUSTOMER table. For example, in Figure 2.11, the CUSTOMER table falls hierarchically under the REGION table, which falls under the COUNTRY table. In a way, these dimension tables form a hierarchy. For example, for each region there are many customers, and each customer belongs to only one region. Dimension tables, on the other hand, can have relationships with one another, and the relationships that exist are all one-to-many relationships. A snowflake schema representation of a subset of the data in the WCM sample database.Ī fact table in a snowflake schema has only relationships with dimension tables, just as in a star schema.