How to limit the incoming data when importing data?

[rodneyc8063]

Hopefully this isnt a silly question, but I was looking for some guidance

I have been dabbling in PBI desktop using DirectQuery, but now I want to share my reports by giving out my PBI file. Not all the people I am sending my PBI file to have access to the database (firewall rules etc).

So I understand if I want to convert a PBIX file from DirectQuery to importing all the data, I can go to the lower left hand corner of PBI desktop, and there is an option to convert to all imported tables. Great.

When I tried to do this, I noticed I was hitting a few errors where I was basically running out of memory, and was not able to fully import the dataset. Fair enough, so this then led me to the question of how do I limit the data for these tables?

Upon further research, I found that if I go to [Edit Queries -> Source -> Advanced Options] I can input a SQL statement. Great again, so I can start limiting the incoming data per table.

Now my questions are - I was wondering is there a way to limit the data in a more central manner vs writing a SQL statement per table under advanced options?

For example, if I have a dimension table with 4 values linked to a fact table with 15 records. Lets say I write a SQL and limit the dimension table to just 1 value, would this also filter the fact table records to only pick up the records that match the 1 dimension table value? Or does this only filter the records on the dimension table itself and has no impact on the rest of the model?

Not sure if my question makes sense.

Im just concerned if I needed to go to specific tables to write individual SQL's to filter tables I would then need to be quite familar with my dataset in order to know what exactly to filter on, and where to filter it. 

Im just wanting to verify if my understanding so far is correct, just so Im aware of how things work.

Thanks for any advice and insight

[jdbuchanan71 · Accepted solution]

Hello @rodneyc8063 

If you apply a filter in your SQL statement it applies only to that table you are pulling back, it will not be applied to other tables pulled into the model.

When working with importing data from SQL the ideal way to do it is design the views the way you want them in SQL Server itself using Management Studio.  That way the DBA's know the items that are using certain tables and if there is an update that impacts those views they will know the views exist.

Then your sql code is just SELET * FROM View

Also, you probably only need to limit your larger fact tables, rather than every table in your model yes?  Pull all customers but only sales for the last 2 years, etc.