Limiting date range import from SSAS Cube

[bosleyjarrett]

Hello, 

I am running into an issue when attempting to import dates from our SSAS cubes. In order to create the report that I want, we have to use fields that have a large number of distinct values (in many cases over 100,000) by date. The default import for the SSAS connection is to pull all the dates available in the cube (early 2012) so as you can imagine, we end up with some queries that are many millions of rows and take forever to load. 

Any guidance here? I'd ideally like to be able to pull in some hourly data that we house in the database but the number of rows that we'd be looking at there ends up being too much for the software to handle and the query times out. can I limit the import before it pulls in?

Thanks

[Greg_Deckler]

Are these tabular or multi-dimensional cubes? Are you importing or live query? If importing, check out the Advanced drop-down when you are connecting to the cube and you can paste in MDX query, etc.

[bosleyjarrett]

These are multi-dimentional cubes and we are importing rather than live querying.

[bosleyjarrett]

Do I have to use a different MDX syntax than what I would use in an excel instance of SSAS? We have multiple cubes on this server and it seems like it does not know where to execute the query.

[v-jiascu-msft]

Hi @bosleyjarrett,

According to my test, we can truncate the query in the Query Editor. For example, I have table with 10 thousands rows. I edit it in the Query Editor to keep only top 200 rows. Then the loading is very fast due to there are only 200 rows. 

If you want to filter them by MDX, you should do it in the initial interface.

Best Regards!

Dale

[bosleyjarrett]

My issue is getting the data into the query editor view. The dataset is very larger and is unable to load into the editor.

[v-jiascu-msft]

Hi @bosleyjarrett,

Maybe you can try to get data in Query Editor. The dataset only has preview in Query Editor. You can manipulate it before applying in the Query Editor where you can reduce the size of dataset.

Best Regards!

Dale