Data Model

[marishnagendran]

Dear All,

Customer Master		Branch		Fact		Item		MasterCalender
Cust_ID		Branch_ID		Cust_ID		ItemCode		%Creation_Date
Customer_Name		Branch Name		Branch_ID		ItemGroup		Fiscal Year
				%Creation_Date				Quarter
				ItemCode				Month
				Qty
				Profit
				NetProfit
BusinessPlan_Fact
%Creation_Date
ItemGroup
Cust_ID
Branch Name
Target
Potential

Above tables are my table structure.I want to do a data model for this.Need you help

2 table (i.e) Business plan and fact table is in separate table.

I tried many ways it generates synthetic key or circular reference.Need your help

[JordyWegman]

Hi,

First, get your facts in the middle. Then try to link all your fields to this fact table. 

1. Concatenate the Business Plan Fact to the Fact.
2. Customer Master -> Cust_ID
3. Branch -> Branch_ID
4. Item -> ItemCode
5. MasterCalendar -> %Creation_Date

Every name that is then double in your tables, that is causing synthetic keys or circular references, rename them!

For example: Branch name from BusinessPlan_Fact -> Branch name as BusinessPlan.BranchName.

This way you don't have any duplicates.

Jordy

Climber

[marishnagendran]

Thanks for your response

Yeah that's the issue i'm facing

but for business plan fact i want to analyse by itemgroup and branch name.

Because i want to compare business plan facts with fact table facts.

For example

ItemGroup	Potential	Target	Qty
A	100	80	20
B	200	90	10

[JordyWegman]

Yes that is possible then, please execute the steps and you will have a good model that you can use.

Jordy

Climber

[marishnagendran]

I don't know whether it is possible or not??

If it is possible kindly share the steps how to do

[JordyWegman]

First model the steps that I have presented, that is the guide to your solution.

Jordy

Climber

[marishnagendran]

If i go with your step(i.e Concatenate 2 fact tables).the following scenario happens

From ItemMasterTable

I'm using Itemgroup wise Potential it won't works.because in Fact table itemcode is linked 

[JordyWegman]

I get the problem. For this you can left join your Item table:

Left Join(Fact)
Load distinct
ItemCode
ItemGroup
From [YourSource](qvd);

This way you won't have this problem.

Jordy

Climber