Concatenating a table into Single Fact

[#1]

!Hi Experts,

We are creating Single Fact Star Schema Model for our application.  While doing so we have concatenated one table to the exiting single fact table. We are able to meet all the requirements from Dashboard perspective except one.

Need your help on the same.

Requirement : Need a STACK Chart for Units and Q_Units at Region_Id

I have attached the Sample file.

Thanks,

Kumar

[hic]

This is in principle easy:

1. Add an expression Sum(Q_Units)
2. Change to "Stacked" on the Style sheet

However, your data has a problem: Your Q_Units are not associated with Region_Id, so the chart does not look very well. In the picture below I have selected all Regions, and then all Q_Units are excluded.

So, you need to fix your data model.

HIC

[#3]

hi kumar please check the scripting part ,there might be some problem in data linking

[#4]

Hi HIC & Rao,

Thanks for your reply. If you check the expression in text box i have written, it  tells how we can calculate Q_Units at Region level.

='Q_Units : ' &

Sum({1<TERRITORY=P(Territory_Id),DIM_TIME_ID=P(DIM_TIME_ID)>}Q_Units)

Since, Q_Fact has been concatenated not joined.

Individually , I am able to create charts from both units and Q_Units but need to collaborate both to have one STACK Chart.

Thanks,

Kumar

[#5]

Hi HIC,

Any idea on how to make this Stack Chart.

Details are mentioned in my below thread.

Thanks,

Kumar

[hic]

It might be possible to create a Set Analysis expression that does this, but I think that that is the wrong path forward. It would lead to an expression that is unmanageable that you will not understand when you look at it in 3 months.

Instead, I think you should fix your data model. Below I have a table box with your data:

You can clearly see that you have a mismatch in your fact table - you have two different territory fields even though they are the same thing. Instead of storing Territory_Id and TERRITORY in two different fields, you should store them in one field in the fact table. With Region in a dimensional table (Territory should be primary key) the chart will work right away with a very simple expression.

Concatenating a fact table is only useful if you have common fields between the two parts.

To get the Territory_Id into the fact table, you can use Applymap (See page 16 in Generic keys).

HIC