SSAS - cube filtering on import

Question

I am new to SSAS and after trying for hours to solve this problem I asking here.

I have a msOLAP cube that I want to import into SSAS PowerBI but due to large database I want to pre-filter it befor importing. The cube has measures in cpe_fact table and many other dimensions i.e. dim_time, dim_product, dim_material etc...

What I am trying to achieve is getting all the fields from the fact table joined with a subset of dimensions (i.e. only dim_time and dim_product) and filter them by date (i.e. cpe_fact.sale_date < now-6 month)

I tried to put this as MDX query but could not get any data by usin this MDX:

SELECT  
    { [CPE_FACT].[MAIN].[SALES_Q]} ON COLUMNS,  
    { [Selected_Date].[POSTING_DATE] } ON ROWS  
FROM [CPE_Analytics]

I get this error: cube either does not exist or has not been processed evewn before I had a chance to define the WHERE part.

I tried DAX :

evaluate(filter('CPE_FACT', [AGENT] >= "26003"))

it worked but only for CPE_FACT table but i didnt understant how to join with the other dimensions...

My Question: How can I import some Facts Join Few dimentions from the Cube? Example SSAS Connection -

It would be best not to import it from the cube, but load it from the tables/views the Multi Dimension cube sit on. Power BI can connect to an MDX cube in Live connection mode, no importing required — Jon
– Jon, Commented Sep 14, 2020 at 7:27

Naro · Accepted Answer · 2020-11-04 15:51:35Z

1

Instead of using an MDX/DAX query use Power Query editor in two steps :

chose the tables you want to import (cpe_fact,dim_time and dim_product).
apply a filter on the date column in the fact table (cpe_fact) to load the desired resultes.

visit : https://radacad.com/only-get-the-last-few-periods-of-data-into-power-bi-using-power-query-filtering

answered Nov 4, 2020 at 15:51

Naro

8107 silver badges11 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

SSAS - cube filtering on import

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related