Forum Discussion

rj21440's avatar
rj21440
Frequent Visitor
8 years ago

data lineage

I'm trying to join tables but I keep getting an error regarding data types and lineage.   The data types are the same (text) but two tables are from diferent sources.   The field to join on is a ...
Need Help
Tips and Tricks
rj21440's avatar
rj21440
Frequent Visitor
8 years ago

Joining the two queries within the Query Editor has resulted in any changes I make to the resulting query taking a long time (30 mins +) this is having reduced the telematics data XML to a single day which is just over 1500 files and 120 MB (We have 3 years in total). The other Access DB is 1.2 million rows and I'm not in a position to touch that, only load it and reduce it with a query as it's live.

 

The ideal solution would be to create a relationship between the two tables after loading the queries so the data does not balloon to a size where the PC is struggling to do anything.

 

This could be done if Power BI could ignore the data lineage and perform something similar to a SQL join. That's why the data lineage appears to be causing a problem. These two sources are related by vehicle registration number/tag and date but come from totally diferent systems.

 

The wider relationship is shown below. I'm trying to traverse the relationship with VehicleFact in the middle. Some of the relationships have been disabled for now.

 

 

Anonymous's avatar
Anonymous
Not applicable
8 years ago

I'm in exactly the same situation than you.

 

It seems that PowerBI doesnt allow LEFT OUTER JOIN relations tables as SQL.

 

I just found one solution but it doesnt seduce me: subject here

I don't understand the aim to merge large tables by creating a new one (and getting multiples colums) when we do know that will reduce the performance.

 

Any help please ?

  • rj3838's avatar
    rj3838
    New Member
    7 years ago

    Sorry for the delay, I have had to create a new account as it wouldn't let me reset the password....

     

    You probably aren't going to like the work around. Basically I'm using R to clean up the data before loading it.

     

    That said the data I have is so large that I haven't been able to load it into memory to do the join in R. So I now have a workaround on the workaround.

     

    There is an R package which allows the data to be used in creating an SQLlite DB on disk just for the duration of a command. It takes an inordinate amount of time to run but has no restriction on the size of the data and allows some complex SQL to manipulate the data.

     

    That requires 'R' to be installed (it's free public domain software and has a Microsoft supported version) and I have some experience with it. Apparently the latest BI has python available as well, but being an R person I wouldn't know much about it.

     

    Did you try to create combinations of columns and use them as an index to link the two ?

     

    Roy