question

FaatinAqeelaGhazali-8849 avatar image
0 Votes"
FaatinAqeelaGhazali-8849 asked LukasYu-msft commented

SSAS cube with multiple database

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
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

OlafHelper-2800 avatar image
0 Votes"
OlafHelper-2800 answered LukasYu-msft commented

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.

· 4
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

I need to make 3 analysis services from every hospital first and then do the SQL above?

0 Votes 0 ·
OlafHelper-2800 avatar image OlafHelper-2800 FaatinAqeelaGhazali-8849 ·

I need to make 3 analysis services from every hospital first and then do the SQL above?

No, the other way round. Create a view on one of the relational database with that SQL code and use the view as data souce in SSAS.



0 Votes 0 ·

I see. Superb idea Olaf, thank you so much

0 Votes 0 ·
Show more comments
LukasYu-msft avatar image
0 Votes"
LukasYu-msft answered FaatinAqeelaGhazali-8849 commented

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.

· 3
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

My main problem is to do make cube from 3 data sources. For example, I have data sources from Hospital A,B and C. Each of the hospitals has Admission and Patient table which I need to make cube from it. Somehow, how can categorize it the by A ,B and C hospital ? do yo get what I mean?

0 Votes 0 ·
LukasYu-msft avatar image LukasYu-msft FaatinAqeelaGhazali-8849 ·

I thinks this is not an analysis issue rather than a SQL database issue?
Did Olaf's suggestion in SQL help or resolve your issue ?

0 Votes 0 ·

Still study about it.

0 Votes 0 ·