Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi everyone
I have a question regarding self service and the impact of splitting large tables into two (or three for that matter). We have a self service solution where we as the BI department provide standard tables in an app and show certain KPIs which are important to the company. The standard tables contain a few columns (let's say 10) of the actual SQL tables (with about 100 columns and many text fields). I was wondering what the impact would be of breaking one such 100 column table into two or more tables which share a common ID field (1 to 1 relationship) and then concatenating them all?
Current:
Machines:
Id | Code | Description | General description | Value | Capacity | Make | Model |
1 | TP001 | Tray packer | Tray packer on line A2 | $200 000 | 1500 | Make1 | AAA |
2 | FM001 | Filling machine | Grey filling machine on line A2 with automatic splicer | $450 000 | 5000 | Make1 | BBB |
What I am planning:
Machines:
Id | Code | Value | Capacity |
1 | TP001 | $200 000 | 1500 |
2 | FM001 | $450 000 | 5000 |
Machine Details:
Id | Description | General description | Make | Model |
1 | Tray packer | Tray packer on line A2 | Make1 | AAA |
2 | Filling machine | Grey filling machine on line A2 with automatic splicer | Make1 | BBB |
This forms part of a larger data model which is actually a snow flake schema. For the standard apps we only need the Machines table (reduced one), but I would like to make the Machine Details available to the end users as well when they need to do that kind of analysis.
Obviously this is a simplified example, but I would just like to get the experts' opinion on the principle of splitting a big table into two and how that will compare to having one large table. Maybe @swuehl or @Gysbert_Wassenaar have some ideas?
Regards,
Mauritz
Your ID field is not a primary key on your current table.
Splitting the tables and using ID as key field will link tray packer to both codes, for example.
Is this what you want and correct?
Hi Stefan
I am sorry. The ID column is actually unique. I changed it in red in my original post. My question is actually what the effect would be of splitting a large table with for example 101 columns (each row with a unique IDs) into two tables. One with 21 columns (ID + 20 columns) and another with 81 columns (ID + 80 columns). Would loading and concatenating these two tables (as a user would do when they add tables using the data manager) be a lot more resource intensive than just loading the table with 101 columns?
Regards,
Mauritz