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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

import data into power bi from azure analysis service (perspective) - can i filter before import?

Hi  
when importing data from AAS and you have the perspective and a set of tables to select your columns from.  After selecting your columns from multiple tables, can you filter the data by a specific value in a column and then import the data?  This is to reduce the quantity of data transfer to the power bi otherwise the AAS runs out of memory due to the volume. e.g i need only 2million records to import to power bi,  instead of the available 10 million rows for the multi-table column selection.
If this approach is not possible, what alternative options are there?  If we import each table individually , we can not join the tables into a relationship because the primary key / foreign key relationships are hidden.  

 
 
 



Thanks 

akthar

8 REPLIES 8

Hi @Anonymous,

 

Can't you access the DWH (source) directly?

In my opinion, AAS is not a good source for a Power BI model.

 

Did I answer your question?
Please mark my post as solution, this will also help others.
Please give Kudos for support.

Marcus Wegener works as Full Stack Power BI Engineer at BI or DIE.
His mission is clear: "Get the most out of data, with Power BI."
twitter - LinkedIn - YouTube - website - podcast


Anonymous
Not applicable

@mwegenerthank you for this valuable comment. Thank you.  I agree with you.  But the team on the other end want us to use their AAS and will not provide the source to AAS.


great article on this point by the power bi dataflow guru Matthew Roche:

Using Analysis Services as a data source is an anti-pattern – a worst practice. It is not recommended, and any solution built using this pattern is likely to produce dissatisfied customers. Please strongly consider using other data sources, likely the data sources on which the AS model is built.
Extraction of large volumes of data is not what an Analysis Services model is designed for. Performance for the ETL process is likely to be poor, and you’re likely end up with memory/caching issues on the Analysis Services server. Beyond this, AS models typically don’t include the IDs/surrogate keys that you need for data warehousing, so joining the AS data to other data sources will be problematic.[2]

Adam Saxton from Guy in a Cube agrees:
https://www.youtube.com/watch?v=GPf0YS-Xbyo
Power BI Walkthrough: Analysis Services Live Connect
at 1m45sec Adam Saxton described the import from AAS into power bi as "there just is a lot of badness there"

Marco Russo in a twitter comment says it is not ideal 
https://twitter.com/ahdatabase/status/1250506863445258255

We need more blogs articulating against using AAS as an import source for power bi - so it enables/helps us to object to business or architects who force us to go down this route. 

Hi @Anonymous,

 

Yes, it's like going to a restaurant and ordering a meal to get the ingredients to cook a meal.

But I'm looking forward to this feature...

https://docs.microsoft.com/en-us/power-platform-release-plan/2020wave1/business-intelligence/composite-models-over-power-bi-azure-analysis-services-datasets

Did I answer your question?
Please mark my post as solution, this will also help others.
Please give Kudos for support.

Marcus Wegener works as Full Stack Power BI Engineer at BI or DIE.
His mission is clear: "Get the most out of data, with Power BI."
twitter - LinkedIn - YouTube - website - podcast


Greg_Deckler
Super User
Super User

Not sure I understand where the issue is, but generally you can filter things on import in Power Query as part of the import query.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
amitchandak
Super User
Super User
Anonymous
Not applicable

Hi @amitchandak 
That's a great link and @thiagozaiden is asking exactly same question i am asking here.  And @Greg_Deckler  gives the solution (in purple italic) but the MDX code is not shown:

Use "Advanced options" when creating your query and paste in your MDX to filter the data at the time of query.

However, I am not familler with MDX.  
in power bi advanced editor the content i have is as below example:  

#"CMT Finance Model1" = Source{[Id="CMT Finance Model"]}[Data],

    #"CMT Finance Model2" = #"CMT Finance Model1"{[Id="CMT Finance Model"]}[Data],

    #"Added Items" = Cube.Transform(#"CMT Finance Model2",

        {

            {Cube.AddAndExpandDimensionColumn, "[Trans_CMT]", {"[Trans_CMT].[WBS_Internal_Number_-_Sender].[WBS_Internal_Number_-_Sender]"}, {"Trans_CMT.WBS_Internal_Number_-_Sender"}},

            {Cube.AddAndExpandDimensionColumn, "[Unit]", {"[Unit].[Unit].[Unit]", "[Unit].[UnitKey].[UnitKey]"}, {"Unit.Unit", "Unit.UnitKey"}},

            {Cube.AddAndExpandDimensionColumn, "[Vendor]", {"[Vendor].[Account_No_Of_Tax_Office_Responsible].[Account_No_Of_Tax_Office_Responsible]", "[Vendor].[Account_No_Of_Tax_Office_Responsible_Text].[Account_No_Of_Tax_Office_Responsible_Text]"}, {"Vendor.Account_No_Of_Tax_Office_Responsible", "Vendor.Account_No_Of_Tax_Office_Responsible_Text"}},

            {Cube.AddAndExpandDimensionColumn, "[VP2]", {"[VP2].[Function_Text].[Function_Text]"}, {"VP2.Function_Text"}}

        })

in

    #"Added Items"

 

How can i filter using MDX language with the following as below?  

VP2[Function_Text]  IN {"GPO" , "GPO Follow-on"}

Anonymous
Not applicable

solution given here by Chris Webb  looks good, I am testing it:
https://community.powerbi.com/t5/Power-Query/Import-data-from-Analyses-Services-without-MDX-for-busi...

at the moment, I am encountering outoff memonry issues...i will try by reducing the number of columns I select to import and see what happens
image.png

 

Anonymous
Not applicable

AAS data source to import.PNG

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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