Creating joins between excel data and Fact table

[fml55555]

Hi Gurus,

I have imported an Excel sheet to a FM model. This sheet contains data about monthly planned revenue for various regions

The structure of the excel sheet is

Endofmonth(c1)            Year(c2)            Region(c3)     Revenue(c4)

The endofmonth column has the last date of each month for current year (e.g. 2014). Each month is repeated for each region          e.g. jan will be repeated 10 times for 10 regions.

Now I want to create a join to a fact table which has more regions (e.g. 25) and has data from all years (not just current year).

How can this be achieved?

Thank you!

[cognostechie]

Joining is not a problem but the problem would be the rollup in the reports if you join two Fact tables. Just to explain the technique to relate the tables together:

Create a calculated column in the Fact table which has data for all dates. Assuming the Date column in this Fact is called
Transaction_Date, the calculation would be _last_of_month(<Transaction_Date>). Join the Plan and the Fact tables using
this column with EndofMonth column and Region column in both the tables.

...but don't do this. This will cause the rollup to be wrong ! In this case, you need to have a Dimension for Regions and
join both the tables to the dimension. Set a unique determinant in the Region table. Also have a Date Dimension and
join both the tables to it. Set a unique determinant to Date dimension also. That should do it.