Essbase Connector not working

[rohitk1609]

Hi,

I have a use case where I need to create dashboard on Qlik Sense and data source is Hyperion Essbase Cube. Qlik provide free inbuilt connector and the demonstration is available with very simple example.

Now, when I made connection between Qlik and Essbase cube , All the parent dimension and measure are coming same in both Dimension and Measure box. Why this is happening ?

Secondly, If I select one parent dimension and one parent measure it is fetching data but when I select two or more parent dimension it is showing me error.

I can't fetch data for one parent dimension at a time and then concatenate later (This solution is available on Community ) because If I do so then there will be never associate data mapping there.

I need to fetch data for all the dimension and measure or the way I can map it later as every object will be associate to each and every object as cube schema.

I am attaching snapshot for the same list of dimension and measure in both of the box and error coming out while I try to fetch data.

[Michael_Tarallo]

Hi Rohit - I am adding a colleague of mine that may be able to assist if he has a moment:

dyo‌ davelyon‌ - Hey Dave - thoughts?

Rohit, --- when applicable please mark the appropriate replies as CORRECT. This will help community members and Qlik Employees know which discussions have already been addressed and have a possible known solution. Please mark threads as HELPFUL if the provided solution is helpful to the problem, but does not necessarily solve the indicated problem. You can mark multiple threads as HELPFUL if you feel additional info is useful to others.

Regards,

Mike Tarallo

Qlik

[dyo]

I thought I had replied to another thread, but I don't see it now.  Make sure that you are on a recent version of Essbase and Provider services.  There was a bug with older version of Provider services that did not properly parse the XMLA.  Also you need to make sure that you break down your query to smaller queries.  Right now you have selected every metric in your outline.  Just like you would never write a single query to pull all data from your Data warehouse you do not want to do that for Essbase either.  I have used a loop like below to break my MDX into smaller more manageable queries.

Loop Through a Dimension to break a query down to smaller queries:

LIB CONNECT TO 'Essbase_54.172.35.52';

// Get list of Quarters from Year Dimension

YearList:

SQL SELECT {[Scenario].[Actual]} ON COLUMNS,

{Descendants ([Year], 1, Leaves)} ON ROWS

FROM Sample.Basic;

Rename Field [Year.Levels(1)] to Quarter;

//Connect to Essbase and get data for each Quarter.

LIB CONNECT TO 'Essbase_54.172.35.52';

FOR Each var_Year in FieldValueList('Quarter')

      EssbaseMasterTable:

                SQL SELECT {{UNION({[Measures].[Sales]}, {[Measures].[COGS]})}} ON COLUMNS,

{CROSSJOIN({[Year].[$(var_Year)]}, CROSSJOIN({Descendants ([Product], 2, Leaves)}, CROSSJOIN({Descendants ([Market], 2, Leaves)}, {[Scenario].[Actual]})))} ON ROWS

FROM Sample.Basic;

Next var_Year

[rohitk1609]

Hi David,

By your solution it seems you have good knowledge of MDX writing.

Yes logically you are right, It supposed to be like this but as Here, number of cross join increase more than 4 so Qlik start giving me error.

What you did above , you made Year as variable and for each year value that written expression will give value, but here with me this long query is not giving me output. It is giving me output where only Year(With its component) is dimension and amount is measure(With all its component).

MDX quires generates by Essbase connector but as basic rule of association  I need to fetch data as how you showed me above but for each and every dimension and thier child component too, then only we I would make a associate model where every component will connect to each and every component.My data size is 10 GB.

In the end , I want measure will be bifurcate to each and every component of dimension.

[Anonymous]

Hi rohitkumar1609‌,

Could you please share how finally you managed to pull those data ? I'm having almost similar situation.

Many thanks in advance.

Best,

Hardik

[rohitk1609]

HI Hardik,

Essbase connector newly launched. Generally Essbase has lots of data like more than 100 GB and when you connect Qlik with Essbase via Essbase connector ,it ask you to select dimension and facts , and you will find both list are same , what exactly happen , if you select partial  dimensions and facts, it will give you data in simple flat file but for making associate you need to select all the dimension and measure in one MDX but this query will not execute because Qlik or any tool can't fetch 100 GB data in one MDX, here you need a MDX query write who can filter data in chunks otherwise there is no way you use Essbase connector. What I did, I asked to fetch all the data month wise and club in Qlik, it was a bad approach but there was no other option.

Best,

Rohit Kumar