Data Modeling Type Question

[michaelsikora]

Hi Everyone,

I am working on a data model for a new dashboard and I am wondering if the below model is acceptable. I added an arrow to show the point of concern. IS_Table and Branch_IS_Table share many of the same fields, but I have renamed them. Therefore, I thought it would be okay to have to same key to tie to the GL (Fact) table. If it's not, how might I be able to improve? Thank you very much for the consideration.

Thank You,

Mike

[marcus_malinow]

Nothing wrong with this at all.

However...

Had you considered concatenating your IS_Table and Branch_IS_Table into a single table?

[michaelsikora]

Thank you for the quick response! Would concatenating these two tables be any more efficient?

I have run the data to show you the relationship to these two tables to the GL. The reason that Total_IS_Format and Branch_IS_Format exist is to format the GL accurately. The IS_Table groups the accounts from the GL appropriately.  While they share some common dimensions, the tables create a unique table format. The reason I can't do the following is because I need to do a right keep on the IS_Table. Unless you know of a possible work around?

Thanks,

Mike

[shiveshsingh]

You can optimise it by concatenating both the tables and use a flag field to differentiate the data in expressions.

[marcus_malinow · Accepted solution]

Ok, I'm not familiar with your data so I'll try to make my answer pretty generic.

If you're ever in a situation where you'll want values from both tables to be shown in the same chart dimension, then concatenation may be a good idea. I'm guessing that the 'LineDescription' fields are being used for individual entries on your GL. Is it likely that you'll end up with records from both for these format tables in the same GL report, or are you going to be creating two variants of the report - one for Total, and one for Branch?

Your question regarding right keep - I'd really have to see the load script to work out if there's a workaround (it's likely that there is - perhaps using EXISTS, or a temporary table)

In short, I don't think you have anything to be too concerned about.

[michaelsikora]

Thank you very much for the flag suggestion. This helps!

[shiveshsingh]

Perfect, have a great day!