Hello,
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,
