Share via

SSAS cube with multiple database

MushiMushi 81 Reputation points
2021-05-03T07:14:02.463+00:00

I have 3 hospital database with the same instance. Now I want to design Statistics of Patients by Gender for each of the hospitals in one graph bar using Power Bi. How should I do it in one cube? Can someone guide me step by step?

SQL Server Analysis Services
SQL Server Analysis Services
A Microsoft online analytical data engine used in decision support and business analytics, providing the analytical data for business reports and client applications such as Power BI, Excel, Reporting Services reports, and other data visualization tools.
1,344 questions
0 comments
Accepted answer
  1. Olaf Helper 47,436 Reputation points
    2021-05-04T08:12:16.693+00:00

    Somehow, how can categorize it the by A ,B and C hospital

    The easiest way is to create a centralized view, which returns the data for all hospitals incl. a sign, from which hospital the data comes from, like 

    SELECT 'A' AS HospitalName, FirstName, LastName, Room
FROM HospitalDB_A.dbo.Patient

UNION ALL

SELECT 'B' AS HospitalName, FirstName, LastName, Room
FROM HospitalDB_B.dbo.Patient

UNION ALL

SELECT 'C' AS HospitalName, FirstName, LastName, Room
FROM HospitalDB_C.dbo.Patient

    If you get a new hospital, you only need to modify the view.

1 additional answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. Lukas Yu -MSFT 5,826 Reputation points
    2021-05-03T09:05:45.82+00:00

    Which particular part did you find problem or issue ?

    Are you having problem with multiple database as datasource ? If so, you could pull data from each of the SSAS database and store them in Power BI table, then add relation between so you could work on it.

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.