topic Need Help Connecting and extracting data from Info Cube in QlikView

Need Help Connecting and extracting data from Info Cube

Tue, 15 Mar 2011 21:36:16 GMT

Hi All,

My work entails bringing data from SAP Info Cubes into QlikView. I have SAP BI Cube QVD Generator.qvw but have no idea how to use it.

How does it work?

Is it similar to the Script Builder?

What are the changes (eg. Connection Srting etc) do I need to make in order to refresh SAP BI Cube QVD Generator.qvw and access relevant cubes?

What are the challenges in bringing data from infocubes?

If someone could guide me or provide a tutorial pdf, it would be a big help.

Thanks,

Neeraj

Need Help Connecting and extracting data from Info Cube

suniljain — Mon, 21 Mar 2011 13:49:53 GMT

Dear neeraj,

both Script builder and Cube extractor are different thing.

I will suggest you that extract fact and dimention from sap bw and make cube in qlikview.

I already done one project with SAP BW.

Regards

Sunil Jain

Need Help Connecting and extracting data from Info Cube

Fri, 25 Mar 2011 01:49:28 GMT

Thanks a lot Sunil. I did the same thing. I extracted the Fact, Dim ,SID and DIM ID tables.

Need Help Connecting and extracting data from Info Cube

suniljain — Fri, 25 Mar 2011 11:32:17 GMT

OK Boss

Need Help Connecting and extracting data from Info Cube

Wed, 18 May 2011 08:18:17 GMT

Sunil

I have extracted data in QVD files but I am stuck in how to use that in my final QV file.

I simply tried to laod all QVD files but its giving Circular key reference error at

first and then Don't show data giving Out of Memory error..

After removing that through Qulifier, I and tried to see data but it again gives similar error ' Out of memory'

I have 8 GB Ram which is hardly being utilised.

Can you please help me

Regard

Thanks in Advance

Need Help Connecting and extracting data from Info Cube

suniljain — Fri, 27 May 2011 04:08:23 GMT

Try to work on limited data and check data modelling , whether any loop exist in data modelling or not.

In case of cube ,circular key reference is not possible because every fact is connected with the help of SID only.

Need Help Connecting and extracting data from Info Cube

Fri, 27 May 2011 06:36:31 GMT

Sunil Thanx for the response.

I have done that but issue of circular reference arises due to DIMID tables.

do we have to load them as well and whats their purpose.

regards

Re: Need Help Connecting and extracting data from Info Cube

Ian_Crosland — Fri, 27 May 2011 15:28:01 GMT

You will need to load all of the following tables for a cube (these can be found by using the function ListSchema in SAP BW)

as an example for demo Cube 0D_DECU:

Fact Table /BI0/F0D_DECU

and Dim Tables: /BI0/D0D_DECUT,/BI0/D0D_DECUP,/BI0/D0D_DECUU,/BI0/D0D_DECU1 etc

in this example the /BI0/D0D_DECU1 Dim table is the link between the fact and the Organisation master and SID tables link as follows:

Fact Table - KEY_0D_DECU1 to Dim table DIMID with an inner join load in QlikView to give you a single table with all the fact data and linked DIMID's, also rename DIMID in the join to KEY_0D_DECU1 to avoid loops.

Next the master data is linked with the S,M and T tables

The Dim table contains fields starting with SID_ this is the key to link to the S tables for the master data. In the S tables the key field is called SID so you need to rename this to match the SID column in the Dim table

from here you can also link M and T tables

The template for SAP cube building is doing this automatically in the script

thanks

Ian

Re: Need Help Connecting and extracting data from Info Cube

suniljain — Sat, 28 May 2011 04:42:37 GMT

In Cube, The Role of DIMID is to connect FACT and DIMENTION table.

cube is nothing but the combination of fact and dimention.

Need Help Connecting and extracting data from Info Cube

Mon, 30 May 2011 05:52:13 GMT

Thanks a lot Sunil,

I really appreciate your help. It has been great since I am working on this for the first time.

Sunil, it would be really great if you can clarify me one more thing..

I can connect Fact table (Transaction) with Master data in two ways..

One through SID_ field directly linked with Master data. Secondly through DIM tables.

But problem, that I see here, is if I Unqualify SID then there is no need for DIM tables, but if I Unqualify.. KEY (After changing DIM to KEY_cube nameU etc), that would not link it with Masterdata. If I qualify both it would create Circular relationship again..

Please comment on this

Regards

Need Help Connecting and extracting data from Info Cube

suniljain — Mon, 30 May 2011 16:38:38 GMT

I will suggest you to go through SID other than DIMID.

Re: Need Help Connecting and extracting data from Info Cube

Thu, 19 Jan 2012 09:48:09 GMT

Hi,

I work in SAP BI and I am co-ordinating with a team working in Qlikview.

Currently a dashboard is being designed in inventory which is based on 0IC_C03 cube.

The cube uses non-cumulative keyfigure for stock quantity and value which doesn't get stored in the cube.

In order to avoid the dependency on marker updates in the cube, I suggested to compute stock value and qty based on the Receipt and Issue fields which is present in cube.

Citing performance and difficulty in handling deltas in Qlikview, I have been asked to store month-on-month Inventory stock value and qty in a DSO in BI for the last 2 years so that Qlikview team can use it to perform computation.

Since the data volume is huge, storing data in a DSO through APD is a tedious process. The APD fails to execute.

I would like to know what is the general practice followed by the Qlikview developers. Would storing the cube data in Qlikview and then performing the calculations in Qlikview be a better solution ? Since I don't know much about Qlikview, I am unable to comment on same.

Help would be highly appreciable.

Regards,

Neha Tank

Re: Need Help Connecting and extracting data from Info Cube

suniljain — Fri, 20 Jan 2012 07:48:34 GMT

Hi Neha,

I alredy worked with this inventory cube. As per my experience I would suggest you to implement the logic in qlikview itsel and then only you can take benefit of qlikview. Qlikview is very fast in data processing.

You can perform calculation like Opening Stock + Receipt stock - Issue Stock in qlikview itself.

Regards;

Sunil Jain

Re: Need Help Connecting and extracting data from Info Cube

Fri, 20 Jan 2012 08:11:10 GMT

Hi Sunil,

Thanks for the prompt response. I shall explain the requirement we have:

Based on the user selected date, the stock value has to be computed, which would mean summing up all Rec + Issue values less than equal to the selected posting date.

Then the consumption for the last 2 months needs to be computed, which is summing up all Issue values for resticted set of movement types less than equal to the selected posting date.

The demand for the next months is obtained from another DSO which has data like MATERIAL / PLANT / M1 / M2.

The final formula is : Stock Value / ((Last 2 months consumption + Next 2 months demand)/2 ) / 25.

Does the performance in Qlikview get affected to do this computation ?

Regards,

Neha

Re: Need Help Connecting and extracting data from Info Cube

suniljain — Fri, 20 Jan 2012 09:02:00 GMT

you have ot bulid logic in modelling itself and stored final data in QVD and use modelled QVD in application.

So you have to calculate closing stok for plant, storage location, Material wise in backroung not during user selction.

Re: Need Help Connecting and extracting data from Info Cube

suniljain — Tue, 24 Jan 2012 10:51:51 GMT

Hii Neha,

Your problem resolved or what ?.

Need Help Connecting and extracting data from Info Cube

Tue, 24 Jan 2012 11:07:55 GMT

Hi Sunil,

It has now been decided to store Material / Plant / Calmonth level Stock Value, Stock Qty, Rec Qty,Cons value for the last 2 years and the further computations is going to be done at the Qlikview side.

Could you guide as to how can I assign points here. I tried looking for options, but couldn't assign points to you.

Regards,

Neha

Need Help Connecting and extracting data from Info Cube

suniljain — Tue, 24 Jan 2012 11:22:34 GMT

just Mark as correct answer or useful answer.