Automatic relations on database with 60 tables?

[test110]

Hi,

I have a MySQL database with 60 tables, every table has a ID field and the naming convension is like the following:

"role_id" in "user_roles" referes to "id" in the "roles" table

I just assumed that if i have this structure set up in the database QlickSense would make the same connections but that does not seme to be the case?

Instead i get some strange suggestions from the wizard:

This has to be possible to get automatically in a reliable way? I don't know if it is related but for some reason QlickSense does not recognize the Primary-Key when i import, even though the "id" field is defined as a Primary-Key in MySQL Workbench.

Best regards

Jonas

[Or]

Qlik Sense joins based on fields with matching names. It is not aware of whatever internal relationships you have listed in your database schema, as far as I know. In this particular example:

Load ID as Role_ID, Name

From roles;

Load ID as Roles_Unique_ID, Role_ID, User_ID

From user_roles;

This will join the tables based on the Role_ID field (which is renamed from ID in roles, and is left as-is from user_roles).

[test110]

Thank you for the reply!
I found the script editor and did as you suggested:

I got this result: 

I am not sure what the extra table is there for?

This feels verry cumbersome, will i have to go in and manually script every column rename in this database, also i will have to keep it updated when the database grows. There are probably 100+ relations in this database to keep track of.. There is no way of actually using the naming convention im following to get this done automatically?

[Or]

I'm not aware of any way to do this natively in Qlik Sense (there might be ways I'm not personally familiar with), though there may be third-party options or you could find your own way to automate it in the underlying SQL queries. Keep in mind that you might have dozens different fields called "ID" in your database (as well as dozens of "Last update date" fields, or any number of others), but Qlik needs each one of these to have a distinct name so you can reference it in your app,so there's always going to be some amount of effort involved. You can use the Qualify command to append the table name to the field name and then Unqualify and/or rename specific fields you want to use as keys, but the keys still need to share the same name.

I'm also not sure what the extra table is about - perhaps leftovers from your previous loads, or perhaps a result of you having the table names in the wrong spot (they should be above the Load statement, rather than the Select, I think).

[marcus_sommer]

IMO you shouldn't try to transfer a sql-scheme to Qlik because Qlik isn't a relational data-base else Qlik used an associative data-model. The official recommendation (as best compromise in regard to efforts, maintainability and performance) is to develop the data-model in the direction of a star-scheme - means merging all measures and keys in one fact-table and having n dimension-tables with descriptive information.

Of course it means some efforts but it's easier as it sounds. Here is a good starting point:

Get started with developing qlik datamodels - Qlik Community - 1485839

 - Marcus

[test110]

Thank you! I guess that is what i needed, thanks for steering me in the right direction! I will read up on this and probably run in to more issues/questions. I felt like something was wrong when it was so hard to import the data in a relational database kinda way.