cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
CPGRIER Frequent Visitor
Frequent Visitor

Essbase (Beta) Connector - Applying Filters?

Hi all,

 

Still trying out this connector, and having some trouble with it.  I realize it's still in Beta, but hoping someone can help me with using 'Cube' syntax...

 

I have figured out how to add items and see a dimension and a measure, but now I would like to apply a filter to one of the dimensions when I pull.  I have tried filtering after the pull and everything blanks out, so assuming I have to do this when I retrieve the data.  But I don't know the proper syntax to make this happen.  Below is what I have so far:

 

let
Source = Essbase.Cubes("http://xxxxxxxxxxxx/aps/XMLA"),
#"essbasecluster-2" = Source{[Name="essbasecluster-2"]}[Data],
AUTORPT = #"essbasecluster-2"{[Name="AUTORPT"]}[Data],
AUTORPT.AUTORPT = AUTORPT{[Name="AUTORPT.AUTORPT"]}[Data],
#"Added Items" = Cube.Transform(AUTORPT.AUTORPT,
{
{Cube.AddAndExpandDimensionColumn, "[AE]", {"[AE].Levels(2)"}, {"Accounting Entity"}},
{Cube.AddAndExpandDimensionColumn, "[YR]", {"[YR].Levels(1)"}, {"Year"}},
{Cube.AddMeasureColumn, "EBITDA", "[EBITDA]"}
})
in
#"Added Items"

 

Where can I place the filter in the above syntax?  I want to filter on '2018' data in the YR dimension.

 

Thanks!!!

 

Chris

3 REPLIES 3
Moderator v-yuezhe-msft
Moderator

Re: Essbase (Beta) Connector - Applying Filters?

@CPGRIER,


I don't have the Essbase data source. I make a test using SSAS cube, I can successfully filter the rows in Query Editor after loading all the data, and a Filter rows step will be automatically generated in Advanced Editor.

0.PNG

In your scenario, could you please post a screenshot about how you filter the Year column in Query Editor?

Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
CPGRIER Frequent Visitor
Frequent Visitor

Re: Essbase (Beta) Connector - Applying Filters?

Thanks for your reply.  Unfortunately, I don't think a screen shot will help as everything is blank.

 

I believe this has to do with my essbase pull not having any parameters for the dimensions.  When I choose the dimensions and measure I don't know how, in the syntax, to apply parameters to the dimensions so that I see data.  For example, for the 'Department' dimension I want to only see 'Technology and Product'.  I need to 'populate' every dimension for this to return data, or that is my guess as to why this isn't working.  But would appreciate anyone who has this connector working to share how.  Again, I can connect and see the dimensions and measures, but the cube won't show me any data until I can somehow figure out how to apply parameters to the dimensions.  It's just like going into SmartView.  At first, nothing appears except the dimension names because the user hasn't updated them with the area he wants to see.

 

I have to admit, the Tableau connector is a lot more straight forward that this.  I realize this is a beta, but I hope the Jan 2019 update addresses this as many of us use Essbase.

 

Thanks everyone!!!

Highlighted
dpjan Frequent Visitor
Frequent Visitor

Re: Essbase (Beta) Connector - Applying Filters?

Agree with this.

 

Been trying to connect directly to Essbase but it keeps crashing because we can't select/input the parameters of dimensions to pull. Would love to find out how folks made this work with large datasets.

 

Thanks!

Helpful resources

Announcements
Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

Virtual Launch Event

Microsoft Business Applications Virtual Launch Event

Watch the event on demand for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Users Online
Currently online: 154 members 1,732 guests
Please welcome our newest community members: