Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
CPGRIER
Regular 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
v-yuezhe-msft
Employee
Employee

@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.

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!!!

Anonymous
Not applicable

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors