I am using ER Studio Data Architect to reverse engineer an existing star schema from Oracle. Some dimensions in my model are implemented as SQL views with direct relationships to fact tables. Is it possible to depict in ER Studio a dimension-like relationship between a fact table and a view and specify the relationship keys?
So far, ER Studio has not allowed me to create any of the standard entity relationships between a fact table and a SQL view. There is view specific relationship, but it serves a completely different purpose than an entity relationship (i.e. dimension to fact).
I understand that it is not the best design to have dimensions implemented as views, however at the moment I have not choice, but to deal with that. Any insight on how to handle views as dimensions in ER Studio would be much appreciated.
Hi Michael. The reverse engineering has worked correctly by depicting the constructs exactly in the way in which you have implemented them. The view relationships are different than table to table relationships. View relationships act simply as connectors to represent the tables that the view pulls information from, and the ability to express which data elements are involved. The views can also contain more complex SQL. There is no concept of a foreign key for a view relationship. Thus it must, and does behave differently than a table to table relationship. You have stated that it is not the best design to have dimensions represented as views, and you are absolutely correct. Reading between the lines, I assume you have implemented the views as a quick implementation to pull data elements for each specific view from one or more tables (master data, reference data) to represent the dimension. However, this approach also departs from best practice: When loading a data warehouse/data mart (represented by your star schema), best practice is to extract the data (ETL) to a staging area, then load the facts and dimensions. This is an important step, as the load processes must accommodate changes in the data. For instance, if source data for a dimension changes, should the dimension simply be updated, or is the change significant enough that it represents a new dimension as of a specific point in time? The current implementation as a view always assumes the former, which could have negative consequences. Therefore, a more robust approach would be to represent a staging area with the schema you have just reverse engineered, then represent the data warehouse/mart using the proper dimensional tables, fact tables and table to table relationships as intended. This also lets you classify your tables as: Dimension, Fact, Snowflake, Bridge, etc. and then within type of table, you can classify further. ER/Studio can even provide an automatic table type identification if desired (which can be overridden). You can also model the transformations from the views to the dimension tables using the data lineage modeling capability in ER/Studio Data Architect. To answer the final question, views cannot be handled as dimensions. This is by design as we adhere to best practice and modeling rules. We have provided you with the capability to design and build a robust implementation. Best of luck!