cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Show  only  | Search instead for 
Did you mean: 
Choose Language Hide Translation Bar
0 Kudos

Provide platform to perform data modeling

Status: New Submitted by Super User on ‎02-19-2023 02:31 AM
2 Comments (2 New)

What inspired this wish list request? 

Studying data modeling, trying to work with complicated datasets which have been (or rather could be...) divided into multiple data tables with virtual joins and working with PowerBI. JMP does have virtual-joins but trying to manage then is very difficult (at least in my opinion) and I would rather use full-joins which make the data tables unnecessarily large.

 

What is the improvement you would like to see?

I would like to see a platform which I could easily use to work with Virtual-Joins (and maybe even with basic table operations). I'm fairly sure for this to work great, it would require its own larger platform like Below is a quick example built with Visio.

jthi_0-1676791546633.png

in JMP if I want to do something like this, I think currently my best option is to use full-joins as virtual-joins are very very difficult to manage in my opinion. I will post a bit better demonstration in few weeks when I can share one data I have been working on, it has more tables and better model.

 

Why is this idea important? 

Would make virtual-joins more useful, would make it possible to model data in JMP, would provide new platform which could be built on.

 

 

2 Comments
mia_stephens
Staff
Staff
‎02-21-2023 01:36 PM
‎02-21-2023 01:36 PM
Status changed to: New

Hi @jthi ,

Thanks for this request. Curious if you've looked at the JMP Query Builder, under the Tables menu?

https://www.jmp.com/support/help/en/17.0/index.shtml#page/jmp/query-and-join-data-tables-with-jmp-qu...

Mia

jthi
Super User
Super User
‎03-15-2023 05:46 AM
‎03-15-2023 05:46 AM

Now I can share the data and model I have as the project deadline has passed. I'm not sure how Query Builder will perform the auto-joins but in my case it does work quite nicely as I have fairly OK data model already built with matching column names. In this example I'm using Projects as it will contain all the tables into that single Project.

jthi_0-1678872068987.png

First thing I noticed you have to be very careful to have correct Primary table before you start adding anything else, or the auto-joins will mess up. Next I would like to have an option to join the tables by using Virtual-join instead of creating massive table as I usually work with a lot of data and I try to avoid duplicating (at least character data) when ever possible.

 

Also trying to manage the column list from Available Columns can be quite difficult in my opinion (trying to work with those t10,t15 and so on) so maybe some sort of option to hide/delete/combine "duplicated" columns somehow could work (especially if they are used as primary/foreign keys for joins)

jthi_1-1678872249757.png

I still think JMP could benefit from visual query building platform but I think I can get somewhere by using Query Builder (especially if it could create and manage virtual joins).

 

Here is the snowflake schema I tried to model this data with (no Date table in JMP "model"):

jthi_3-1678872408946.png

 

And here is how it does look like in PowerBI (it was a requirement to use PowerBI in this project):

jthi_2-1678872331452.png

 

The data I have used here is freely available (besides my own modifications to it) and if there is a need for it I can share it with developers (and most likely also the PowerBI file and powerpoint presentation if needed).