SSAS Newbie Question

[LHSCNaved]

Hi All,

Need some thoughts about the following Scenario.

I have two Patient Related Fact tables, one has patient Visit Number, Diagnosis, Discharge Date, Length of Stay,...... and
and the second one has the Visit Number, Lab Order Date, Lab Test,Lab Result ...... both have Visit Number in common.

First Fact table have 36000 records for each year and second fact table has 1.6 Millions records for a year.

I have created a Table which have all the columns in the above mentioned Fact Tables and used the Table in the Cube. The problem I am having is the Average of (Length of Stay) as the number is very high (because now Avg. is being calculated based on 1.6 millions records.
How should I address this scenario ? Eventually I want to generate the numbers by Calendar Year for both Fact Tables.

Any thoughts in this regard will be appreciated.

Naved Altaf

 

[RiverGuy]

What you do is to not combine the two fact tables into one. Leave them as separate tables/measure groups.