Forum Discussion

MP_123's avatar
MP_123
Microsoft Employee
10 years ago

filter on import data from ssas

hi,   i have a problem that my cube is very very big so i can't import the whole data to PBI (i don't want to connect live because i want to be able to add measures, formatting and stuff) how can ...
Need Help
Anonymous's avatar
Anonymous
Not applicable
10 years ago

As is, this procedure is not really correct. It's flawed yes.

 

You have to import and then filter out data OR as I noticed.

 

You can import small amount of data, filter it and then add the remaining columns or dimensions continuing to filter as you go.

 

It's messy, but effective.

 

On another note.

PBI team: Please provide pre-filters for this? :)

MP_123's avatar
MP_123
Microsoft Employee
10 years ago

Anonymous

thanks!

 

do you mean by "table.selectRows"?

because it doesnt work for me...

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    10 years ago

    1) Get data - Analysis Services

    2) enter your servers name etc

    3) Assuming you're importing data (not live connection): Select your timedimension and simply load it all.

    4) Edit Queries and filter the year to this year for example. 

    5) On your right in Query settings under Applied Steps - click on the gear sign right to "Added Item" and simply add the dimensions you need and Load them each time. Filter as much as you can each time as well.

     

    For me this works better than loading all in one which either takes way too long or doesnt work at all.

     

    This is something that we need to be able to pre-filter before loading IMO.

     

    Hope this helps

     

    • birdie29's avatar
      birdie29
      Advocate II
      9 years ago

      Hi Anonymous

       

      I've just seen your comment on the OP's issue and thought this looked like a great solution, however when I try this the filters are removed when adding fields so it doesn't make any impact. The reason being is that when clicking the 'cog' you're effectively going back a step to the pre-filter view.

       

      Am I missing something?

       

      Thank you

      Chris

      • AndyTrezise's avatar
        AndyTrezise
        Advocate IV
        8 years ago

        Hi

         

        I wondered if you found a solution to your problem?

         

        I have the same issue...i need to filter data from the SSAS cube at source as there are far too many rows to pull into PBI

    • MP_123's avatar
      MP_123
      Microsoft Employee
      10 years ago

      Anonymous

      thank you for your fast answer

      i wanted to filter the data due to memory settings, and after i filtered its still having memory issue:

       

      DataSource.Error: AnalysisServices: The Auto Exist operation is exceeding the permitted memory limit and is being canceled.
      Details:
      DataSourceKind=AnalysisServices

       

      do you have an idea to how can i bring the data to pbi?

      • Anonymous's avatar
        Anonymous
        Not applicable
        10 years ago

        Do you have high number of columns ? Try to limit them as you can.

         

        Now I'm thinking you need to write your own MDX formula for your desired query to do this.

         

        Never received memory limitations working with cubes before myself.

         

        I'm all out of answers really... Good luck MP_123