Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
Hi @Anonymous,
Can't you access the DWH (source) directly?
In my opinion, AAS is not a good source for a Power BI model.
@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:
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...
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.
@Anonymous , see this can help
https://community.powerbi.com/t5/Desktop/Filter-Analysis-Services-before-Importing-Data/m-p/58851
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:
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"}
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
User | Count |
---|---|
139 | |
113 | |
103 | |
73 | |
63 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |