This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(60.8, 85%, 15%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EM%3C/text%3E%3C/svg%3E)
I have 2 dim table from different data source, can we merge it using MDX query or do we have other way to do it?
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.
Answer accepted by question author
A vague question.
No, you can not merge it on MDX side, but in the relational data base. Create a view and merge the base table using UNION ALL, like
CREATE VIEW dbo.v_MergeDim
AS
SELECT Col1, COL2, Col3
FROM dbo.Dimension1
UNION ALL
SELECT Col1, COL2, Col3
FROM dbo.Dimension2;
As I wrote, you can do it in the relational database or other option in the SSDT project => datasouce veiw, by changing the table into a named query with the SQL code as above
Thanks Lucas for answering my question, tried the SQL above but it does not work for me, I don't even know what I am doing right now. This is my DSV designer 
to categorize hospital and gender for each of the hospital I create new Database at MSQLSERVER that have hospital, region and sexes table and then I add new relationship between both of the table. The database for the both hospital were from 2 different data sources. What I want to visualize is like this =>
. Hope u understand what I trying to explained.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(252.8, 9%, 34%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3ELY%3C/text%3E%3C/svg%3E)
The two hospital data are stored in two databases as two tables?
I thinks this table structure is not very good to read, we'd better split the table into fact and dimension table and sign each hospital as a dimension factor. This would need the table reshaped from scratch on.
Have you tried SSAS tabular model? Maybe using tabular and count through each table would achieve this goal more easily.
No, we could not merge two dimensions. If you want to merge two tables, you would need to in DSV level to create a new table which unions two table as Olaf has written. In the existing cube, if you have had two dimensions we could not merge them.
Yes I just realize about it, Thanks. One more question, can we create new table on dsv level? I have tried a few time but it does not work. If yes, can u show how?
can we create new table on dsv level?
You can not create a new table in DSV, you have to create the table in the relational database, then you can add the new table into DSV to use it for a dimension.
Hi,
What I mean was not actually create a SQL table but to create a Named Query in DSV which would work like a table in SSAS cube project. Sorry for my vague and incorrect description.
You could follow the tutorial and step in: Define Named Queries in a Data Source View (Analysis Services I think this is what you need in the scene.
Olaf has answered you precisely, if you are talking actuall SQL physic table we cannot do it in DSV.
Have you resolved this ? Could we assist further?