union of two fact tables

[Mita]

I am doing a change in some reports where the source fact table(A) is changed to a new table(B).
So I have created the relationships between the existing dimention tables with the new fact table(B)

The reports are giving correct data.

The old fact table(A) contains historic data where as the new table(B) contains recent data.
They can not merge in DB side because it has many millions of data.

Now If they want historic data in report.So I have to do a union.but with a condition that the old fact table(A)s reporting period < new fact table(B)reporting period
Can any body suggest where should I perform the union ?in FM or in report studio.will it create a loop if i well create a union in fm?

In FM-its two layer achitecture...:(

Plz help...

[bdbits]

I do not see why it would cause a loop.

If you are ever going to want to do it beyond this one report, I would put it in FM at the database level. An even better approach might be to create a view in the database that does the union, and base the FM query on the view. This may perform a bit better.

In any case, I would name the query as presented to the end user such that it is obvious it unions with the historical table.

[Mita]

Thanks for the reply.

I had started doing the same in report studio.Its coming correctly.
But I am facing a weird issue.In my prompt page I have one company prompt which is cascaded to period prompt.Both the prompts are taking value from 2 dimension tables.

The new fact table has data from 201604 but the old fact table has data from 2011.
When I am selecting period as 201403 I am not getting any company ?How this is related to facts.

I am not able to understand.

[bdbits]

This will depend on the tables and columns you are selecting and how the joins are defined. It is difficult to say without knowing this information.

If I were you, I would take a look at the generated SQL in Report Studio. This may point out a faulty join or perhaps a need to for additional query parameters.