Calculation dimension needed

[#1]

Hi all,

I had a scenario  like i have sales fact table and purchases fact table and one dimension table model master,

my requirement is in one report i am showing model wise sales and purchases things in pivot table, but in data modelling i don't have a relation for purchases and sales, and also model master dimension table i created duplicate for purchases table.So for sales fact table one modelmaster table i have and purchases fact table one modelmaster table, now i want is i need a calculated dimension which is having both sales model field and purchases model field as one dimension in report.

Thanks in advance for all!

[Anonymous]

Hi,

we do have option of adding calculated dimension in straight and pivot table as well. If you are looking for adding simple calculated dimension then u can go with this else u can share ur sample data so we can get more idea about ur modeling part.

Thanks,

[Gysbert_Wassenaar]

You can't create a new calculated dimension by combining two unrelated dimensions. You will have to remodel your data in the script. I suggest creating a combined fact table that contains both the sales and the purchases.

[#4]

What you told is nice idea, But i have tables, but i  have 4 relations fact tables for sales part and  six relational fact tables for purchases part, if i do concatenating of them will get a chance of in consisting results and also circular loops.

[Gysbert_Wassenaar]

No, if you concatenate all the fact tables you will have only one fact table and no chance of inconsistent results and circular loops.

[#6]

i will completely explain my data model

SALES:

BILLMASTER--> BILLDETAILS--        <modelmaster,branchmaster

--->

Purchases:

Purchases--> purchasesdetails-->...

-

Purchasesreturns-->purchasereturndetails-            ->modelmaster,Branchmaster

if do concatenate all this fact tables i may get data consistency issues ,,,,,

[Gysbert_Wassenaar · Accepted solution]

I doubt it, but if you're sure you will then you won't be able to solve your problem. You cannot create a calculated dimension from two unrelated fields.