Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
tobiasnygren
Resolver I
Resolver I

How to avoid loading dimensions that not exist in the fact table

How do you avoid loading dimensional data not needed to the dataset in the best way.

 

- When I have worked with SSAS, I have used "native queries" and checked if the dimension exists in the fact table in the query.

 

- I also know that I can filter this out in views, but then the view is not usefull for others.

What do your recommend?

1 ACCEPTED SOLUTION
v-eqin-msft
Community Support
Community Support

Hi @tobiasnygren ,

 

According to my understand, you want to keep rows in Dimension table matched from Fact table.

 

You could select Dimension table --> use Merge-->Right Outer-->Remove unnecessary columns as shown below:

1.21.3.1.gif

If the data is continuous, you could add the whole column in Fact table as new Query ,then use it to create a parameter to filter the column in Dimension like this:

1.21.3.2.gif

 

Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

9 REPLIES 9
v-eqin-msft
Community Support
Community Support

Hi @tobiasnygren ,

 

According to my understand, you want to keep rows in Dimension table matched from Fact table.

 

You could select Dimension table --> use Merge-->Right Outer-->Remove unnecessary columns as shown below:

1.21.3.1.gif

If the data is continuous, you could add the whole column in Fact table as new Query ,then use it to create a parameter to filter the column in Dimension like this:

1.21.3.2.gif

 

Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Looks good, I will test that!

tobiasnygren
Resolver I
Resolver I

If I don't want to use Native queries.
Can i do this check/filter on my dimensiontables in power query with Table.SelectRows?
If the key exists in the fact table, it should also exists in my dimension table.
If not exist in the fact table, don't add the row to my dimension table.

How is the syntax for that if it is working?

Daviejoe
Memorable Member
Memorable Member

You would disable the load of these tables in the Query Editor

 

Right click on the tables you do not wish to load and uncheck the enable load.

 

Performance Tip for Power BI; Enable Load Sucks Memory Up - RADACAD





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




I must load the dimensions that exists in the fact table.
Have you understand my Q correct?
ExamplePic.png

Hi, 

 

apologies, I didn't understand your question correctly.

 

Can you explain a bit further please?





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




As you see in the picture
pk_installations 50-100 not needed for filtering the fact table.
I don't want them to be loaded to the dataset.
In the real tables I have 3million of rows in the dim table, but I have incidents on maybe 1 miljon of the dimension rows.
So 2 million of dim rows not needed. Will take time to load and will consume memory.

Anonymous
Not applicable

@tobiasnygren  you can also make use of native query(SQL)

in power bi as well..What is your data source?

 

Yes I know.
And one of the reason for asking is that I saw this video.

Native Query: Be careful when using in Power BI - 
https://www.youtube.com/watch?v=DWbh7xp08l0


And  I want to find the best and recommended solution for it before using Native Query.

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.