SSMS/SSAS Coding MDX/DAX Query or SQL code for Connecting through SQL Server or Analysis Services


I’m running into some issues with the size of my data, and was wondering if anyone could steer me towards success. I’m working with a company that had data build from SQL tables from two different ERP systems. On top of that, the systems operate on separate servers, and each have their own databases.

Both data warehouses have tabular and cube data, so I can connect either through SQL Server or SQL Server Analysis Service. It seems most of the analyst tend to go towards the Cubed data through Analysis Services, but I am getting data overload even at a aggregated level, just trying to pull data going back two years.

So I have resorted to the prebuilt SQL tables. And then I am pulling all my fact tables and look up tables and then starting to apply filters and remove columns in query editor. Some of what make the data even harder to work with is I’m trying to show sales combined across both servers, so I have appended the fact and lookup tables so I can have one table to build by relationships off.

One fact table that has the transactional data from both ERPs, and many lookup tables that contain the data from both ERP systems.

I am trying to narrow my results down to 247 customers from 10/1/2018 - current. I don’t care if all the columns come in, and then I have to remove some. I just don’t know enough about SQL or MDX/DAX to write any code to put a front facing filter, except for Microsoft, which I’m having a hard time understanding.

This is what my query looks like:

And this is what my model looks like:

Still working on completing the relationships, most will be a combination (concatenation) of a calculated column containing company and customer ID.

To give you a little insight, there are 4 companies or Business Units (BUs). Two in each ERP system. One ERP system handles bulk sales in two countries, and one ERP system handles bagged product in the same two countries. I feel the model works, I just have no idea how I can filter it on the front end.

Any resources, material, suggestions, or alternative solutions for connecting to the data or building the model would be very helpful. I so have SSMS installed and have very limited knowledge with the software so far. I would assume some script would be written in SSMS and then copied to the get data section in Power BI when I’m connecting to the server and database.

Thank you,

1 Like

Hi @ibesmond,

It should be quite straight forward to write simple sql statement with WHERE clause to filter out data. You can test using SSMS and then paste in Power BI to bring only required data. If you have access to database, you can write below query and send me the filter condition, so i can write simple sql for you.

SELECT TOP 100 * from [tablename]




Would this be what you are looking for:

USE [Database]

SELECT [Company]
,[Document Type]
,[Document Type Label]
,[Item Number]
,[Location Code]
,[Sales Territory]
,[Salesperson ID]
,[Document Status]
,[Void Status]
,[Customer ID]
,[Document Date]
,[Actual Ship Date]
,[Requested Ship Date]
,[Currency Index]
,[Bill to Address Code]
,[Ship to Address Code]
,[Markdown Amount]
,[Unit Cost]
,[Unit Price]
,[Metric Tons]
,[Shipping Weight]
FROM [dbo].[Sales Transactions]

WHERE [Document Type]=“3” && [Document Date] >= 10/1/2018 && [Customer ID]=“5565, 7783, 9968, 5573, etc”


I added the Where for the fields that I want to filter.


Yes, you would use same command. You just need to use where clause like below:

  1. single quote for string, if it is number, you will not use single quote
  2. IN clause for multiple values
  3. AND for and condition

WHERE [Document Type]=3 AND [Document Date] >= 10/1/2018 AND [Customer ID] IN(5565, 7783, 9968, 5573, .etc)

Please let me know if you get error while running it through ssms.

1 Like

Hi @hafizsultan

Getting this error. Invalid column name ‘ABC’. The Customer IDs are Alpha numeric with a hyphen.

I added single quotes ‘ABC-141’ and it ran. Thank you for the support!

1 Like

Hi @ibesmond,

It’s great that you find out this error by yourself. Hopefully, you will find SQL easy in the future.

Kind Regards,