Forum Discussion
Help with database structure OR relationship schema!
Hi,
I'm attempting to create a repository of all my teams Power Bi reports, showing all of the datasources, and all of the notebooks which are used to populate these data sources. Part of my challenge is that there are multiple datasources used in PBI reports, and each notebook can output to multiple datasources.
On top of that, I don't want to build a complete database (i.e. with a new table illustrating the datasources for Report1, Report2, and the same for tables which make up Datasource 1, 2 etc) and it needs to be simple to update for the user, so I'm currently separating multiple values in the same column, using a comma.
Currently I have
Table1:
| ReportName | DataSource |
| Rep1 | DS1, DS2, DS3 |
| Rep2 | DS4 |
| Rep3 | DS1, DS5 |
Table2:
| DataSource Name | Related Tables |
| DS1 | T1, T2, T3 |
| DS2 | T4 |
| DS3 | T1 |
| DS4 | T4 |
| DS5 | T1, T2, T3, T4 |
Table 3:
| Notebook Name | TablesUpstream | TablesDownstream |
| Nb1 | T1, T2 | T3 |
| Nb2 | T4, T1 | T3 |
| Nb3 | T1, T2, T3 | T1 |
I was hoping that I could use Table1 (for example) and then duplicate this table, split values by delimiter and then build a relationship to this table [that works] and then connect this in a similar manner to the notebooks - in the end giving me a nice matrix/table visual where a user could search through the list of reports, and quickly see details about that report, which datasources it connects to, and then which notebooks they might need to look at to adjust any final output.
Perhaps I'm trying to find a simple solution to a complex problem here, but it feels like it should be possible!!! (-:
2 Replies
JohnM1985 , I think split by delimiter into rows, is the best solution for this problem for table 1 and Table2, and if needed for Table 3 too
Power Query Split Column , Split Column By Delimiter: https://youtu.be/FyO9Vmhcfag
Thanks amitchandak ,
The problem that this seems to bring is the relationships between the tables, as table 1 goes from:ReportName DataSource Rep1 DS1, DS2, DS3 Rep2 DS4 Rep3 DS1, DS5
To:ReportName DataSource Rep1 DS1 Rep1 DS2 Rep1 DS3 Rep2 DS4 Rep3 DS1 Rep3 DS5
But I don't want that to display with multiple names (i.e. Rep1's) to the user - so I can build a relationship from my original table, to the delimited one, but then to connect this to the next tables I end up with a many-many relationship which I'm trying to avoid, or by using bridging tables with 1 to 1 relationships, which doesn't seem to work.... I wondered if there was a more creative way to get to my solution
