ssasolapmultidimensional-cube

SSAS Two Data Sources in one Cube

I have 2 data source which is Hospital A and Hospital B that have same structure.

On Data Source View , I add same table for Hospital A and Hospital B like this -> enter image description here

then I add hospital_key attribute in the fact table which are table ADMISSIONS to uniquely categorized by hospital.

then I set ADMISSIONS table as fact for each of the hospital. Then the cube would be like this enter image description here

My problem is

  1. I able to visualize just one hospital only like this-> enter image description here

How can I visualize for the both Hospitals? Please Help!

or Did I do wrong?

Solution

  • I think you are asking is how to rationalise your source objects in the Data Source View?

    If so, the tables have the same structure for both Hospitals so you could UNION them together in a named query e.g.

    SELECT MyColumns, 1 AS hospital_key FROM AdmissionsTable1 UNION SELECT MyColumns, 2 AS hospital_key FROM AdmissionsTable2

    If needed, repeat for patients data. I am assuming that you only need 1 Sexes table likewise.