Modeling relationships to views that have no defined keys or indexes

I've been modeling data ad-hoc for a few years now, I'm curious dilettante, no expert. I've recently found myself the Data Engineer for a mid-sized financial company, tasked with building Data Marts for self-reporting.

I've been given a design spec that includes complex db Views. The Wizard doesn't render views (as previously noted) so I've built them as Entities, marking them with their own "View" category. Now I'm integrating one of them into the larger model (snowflake fact with mostly dimension tables, one dbo.category table and 2 views)

The Views have no indexes or keys. The joins are semi-complex, joining to the fact table with a WHERE clause with conditions.

Question: How do I model this in Toad Data Modeler? The Relationship Properties (RP) works well with defined PK and FK's but not with less structured join conditions. The RP General tab Parent/Child box is empty with no method to create user-defined joins or more complex WHERE clauses. ----at least that I've discovered.

I'm not finding any clues in the Forums or google in general. I'm hoping some experts here will enlighten me.

Thank you for your time.

Hello Matthew,
we would like to help you, but we are not really sure about few things:

  • you are writing about some wizard that doesn't render views. Do you mean some wizard in Toad Data Modeler?
  • what database do you use? You are writing about snowlake. Do you use the Snowflake database (unfortunatelly we don't support this DB) or snowflake schema?

Unfortunattely Toad Data Modeler doesn't support modeling of complex JOIN structures, but you can write your own complex views by checking SELECT in Views as Text checkbox.

and then write your own SELECT statement in SQL tab

as you can notice, after checking the checkbox, some of tabs at View Properties form were disabled. Don't worry if you have some settings there, those will be preserved but wouldn't affect the generated code of the view.
At the same time you can create View Relationships between entities and this view to illustrate relations in model visually, but those also wouldn't affect the View till the checkbox SELECT in Views as Text is checked.

Regards, Michal

Michal,
Thank you for your response and guidance. I am using Toad Data Modeler (TDM) to model a data pipeline that maps source data to data warehouses and data marts.

The primary data source is SQL Server 2016. There will be secondary data sources in the future of various types.

I noted your instructions to manually create a view in TDM and followed your instructions with the following results:

image
Side by side comparison of the two methods of creating a database object to represent a view in the TDM tool.

Using your recommendations, I created a proper TDM View (left) but with no Attributes (other than in SQL Preview) to use as joining conditions to other db objects.

Using my incorrect method, I created an Entity, with attributes, and subsequently added Primary Key identification to attributes that act as keys. At least this way I can create a Relationship to the Fact Table..... This is a "kludge" but using your recommended technique, I'm not seeing a way to create relationships with other db objects.

I can create a non-identifying relationship between my fake "view entity" but not from the "SELECT in Views as Text" view I created under your instruction.

My question is, how do I represent non-identifying, user defined relationships between SQL Server Views (as source) to a data mart table as destination?

Perhaps my knowledge in this area is limited or am I on the right track but the tool is limited?

Thank you.

First of all, you method is not incorrect. It may be more suitable for the job you need to be done. It depends what you expect from the model. To be only visual help or to produce workable code?

The compromise (workable code and visual representation) would be to follow the procedure described in previous post and than paint your visual lines by using Line tool or View Relation Tool. Those lines don't affect the generated code and you will have some guiding to quicky catch on what's going on. If you want to use entities and relationships instead of the view please note that relationship can connect only entities via primary key, alternate key or unique indexes.
It's standard behaviour of Toad Data Modeler that when you check SELECT in Views as Text checkbox (because you need to create complex JOIN rules), attributes hide from visual representation.
Also please note TDM is primary E-R modeling tool and could has some limitations in representing of data mart or data warehouse structures.

Michal,
Thank you again for your help. My goal is not to produce workable code, most of the code and ETL processes to to create these objects will be written by myself manually. I think I'm using TDM more as a guide to flesh out requirements. The discipline enforced by this tool forces me to think about how objects relate to one another, cardinality between related objects, etc.

I think of it more of a tool to generate a Requirements Document, a visualization tool to present to my team for review, and to generate documentation for future reference.

I believe I understand a little better, the limitations when using this excellent tool for modeling Datamarts and Datawarehouses.

Thank you for your advice.

Hello Matthew,
glad to hear I've helped you a little. Also thanks for your feedback and usecase you've provided. It's important for us to get these insinghts to better understand our customer needs and further improve the product.

Have a good day,
Michal