Essbase (Beta) Connector - Applying Filters?

[CPGRIER]

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

[Anonymous]

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



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

Regards,
Lydia

[CPGRIER]

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]

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!