Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi
I am sorting a data model that has evolved over time into something that now looks like the Flying Spaghetti Monster.
A lot of the Fact Tables have many to many relationships between them.
I would like to concatenate these Fact Tables but that seems easier said than done. Is this sensible or would Link tables be better ?
Many Thanks, Bill
You can concatenate like this
I think you can go for concatenation like this instead of link table as per the data you have
Rule of thumb:
'If the data is big - opt for concatenate, else link table.'
By any chance if you have missed this: Concatenate vs Link Table
If this all fact table have relationship between them then it is better to create the link table.
Thanks for your inputs.
Attached are two sample qvw's.
199535Link.qvw
199535Concat.qvw
My knees jerk is to Concatenate, but I cannot get it to behave.
The real data is few hundred million rows and shall soon grow to 1 billion+
Hi bill.markham
After looking at your concat.qvw I was wondering why you made the LinkAB table. That is just a resident of the concat table which already has all data in it.
And you wrote that the dimensions do not behave in the concat .qvw. But i think that they don't behave right in the link table. For example:
When selecting XXX in the DimLinkOne both value A and value B are associated. Next i add the selection GGG in Dim Two. GGG isn't connect to value B at all. But in the link table it shows 300 as a sum of value B and in my opion that isn't correct.
Of course i don't know the business rules behind the data model. Maybe you can explain it a bit more for me?
I think you create the link table like this
You can concatenate like this
I think you can go for concatenation like this instead of link table as per the data you have
It looks like you want to aggregate Value B and Count B values over Dim Two values. I think that means a join and thus a link table. So I don't think concatenate will work for that.
Your Link Table suggestion seems to result in some double counts in the front end.
Your concat suggestion looks good.