data model

[surajap123]

Hi Experts,

I have these 3 tables. All 3 tables are linked by a single field PersonID.
Each table contain both dimensions and measures. Infact the tables are sql views created for reporting requirement.

The tables has persons data.
Table1 has all the persons information. If any person has any issues, that will be seen in table2 and if any person has Activity then that is seen in Table3.

The issue is, Table1 has has dimensions like country,location,status that applies to data in all tables.So I want to crate a star schema, where i can put all measures in single table and dimensions in other table.

Please suggest.

[surajap123]

My main issues is, I am not sure how I can take off the dimensions and put in separate table, which link to the fact table.

Can I load the table twice, ie in 1st load I load measures and in 2nd load I load dimensions. Does the association works properly?

[Anonymous]

Hi,

You have to take care to add the persons with Activity in Table 2 and persons with issue in Table 3. Otherwise, it will show only those that have Activity and Issue in the same time.

I would keep table T1 and concatenate T2, T3, and put a flag, 'Issue'  and 'Activity'.

Best regards,

Cosmina

[marcus_sommer]

If I understand your data-structure right then is table 1 a dimension-table and the tables 2 and 3 fact-tables. Quite probably I wouldn't change anything and use the tables with their associations like they are. By larger amounts of data it could be useful to concatenate the fact-tables. I think anything on changes of the datamodel more wouldn't be needed and would rather end with the contrary of an optimization.

- Marcus

[surajap123]

Thanks for all the suggestions.

marcus_sommer‌ - Table 1 has both measures and dimensions. Infact, there are more measure fields in table1 which I haven't mentioned in the diagram for simplicity. Table1 has subset ratio 99% and table2- 33% and table3- 5%, when they all link by personID.

Another reason to change the model is, I want to link all my dates to a single calendar.

Looking at the subset ratio, do you suggest anything to me?

I am thinking to concatenate all tables with flags as suggested by cocosmina‌. But doing that I am having null values for table2 and 3 rows. eg- country, region fields got nulls. Shall I do applymap here?

[marcus_sommer]

In this case there would be no easy and direct answer how you could create a single fact-table and one or more dimension-tables from your 3 tables - it will be depending on various factors. But again if you are not handling with large amounts of data the association of the tables per PersonID might be sufficient.

Beside them if you are concatenate tables like above mentioned you don't need to worry about NULL's - it will work smoothly and fast.

- Marcus

[surajap123]

Hi Marcus, Thanks for the suggestion. I will go with concatenate approach and get back to you.

Marcus Sommer wrote:

In this case there would be no easy and direct answer how you could create a single fact-table and one or more dimension-tables from your 3 tables - it will be depending on various factors.

Just wondering what are the factors I should consider. Could you mention few.

I am still learning data modelling and not confident yet. I have read your blogs on 'datamodeling topics'. But not sure what more resources I should concentrate for my situation..

Please suggest.

[marcus_sommer · Accepted solution]

The main topic will be the the relation between the tables - are they 1:1 or 1:n or n:n - then if your aim is to create a new fact-table which content beside all necessary key-fields all measures you need to look on the table-relations to decide if you could concatenate, join or map these tables and you might be need additionally steps to include counter-fields (to calculate some fields correctly) or to get rid of different granularities within the tables.

Also the various date-fields could make things complicated and you might need in the end several master-calendars and/or a canonical-calendar.

It's always good by developing datamodel to keep in mind the best practice approaches for it - but you need also some degree of pragmatism to reach a solution. At first comes the business-logic and if they worked (and there is really a demand) you could think on optimizations.

- Marcus

[surajap123]

Thanks you so much for the highlights.