Missing data in SQL server after using EVSND

[Former Member]

Dear all,

I am using BPC 5.1

I am trying to send data to BPC server using EVSND and the message indicated that data transfer is a success.

However when i tried to use Microsoft SQL Server Management Studio to query the relevant data line from tblfactfinance, it's simply not there.

Any idea?

Cheers

[Former Member · Accepted solution]

Hi Lip

You should see tblFACTWBFinance table.

Regards,

Halomoan

[Former Member]

Hi,

Halomoan is right, you will find the Data in tblFactWBFinance.

BPC uses 3 Database Tables to store the Data.

tblFactAPPNAME as Longterm storage (MOLAP)

tblFac2APPNAME as Shorttern storage (ROLAP i think)

tblFactWBAPPNAME as Real-Time Storage (ROLAP)

If you send Data via Inputschedule only the different to the value in Longterm Storage is stored in the Writeback table. For example if you have a value of 100$ entered in the System and the cube is optimized the 100$ is moved to tblFactAppname.

After changing the value to 80$ via Inputschedule a record is generated in the Writeback Table with a value of -20$.

Processing the Cube will merge these 2 records to the Facttable (facttable 80$, WritebackTable empty)

Look in the Admin Help, Keyword "Write Back" and you will find a detailed description about Processing Options and storage.

Regards

Jörg

[Former Member]

Hi guys,

yah ... i have already verified that halomoan is right. i have given the full 10 points to him hence i am sorry i can't give you the full 10, jorg.

i appreciate the additional input though.

cheers

Edited by: lip chean soh on Sep 16, 2008 10:43 AM

[Former Member]

One addition, the Fac2 table is MOLAP instead of ROLAP. Which basically means that it should be processed before you can see the data that is in here. The data for MOLAP partitions (FACT and FAC2) is stored in the cube, while the data of a ROLAP partition (FACTWB) is stored in the SQL table and should be queried by the OLAP engine at runtime. That's why a huge number of records in the FACTWB slows down the performance. The lite optimize moves the records to the FAC2 and processes them incremental, so the user can query the data also when it is in FAC2.

-Joost

[Former Member]

So Fac2 is also MOLAP, I wasn't sure, thx for Info.

Thx Joost.

Btw: ever been in Rome with OS5? Think I remember your name

Regards Jörg

[Former Member]

Yes I was also there, I remembered yours too

[Former Member]

Hi Joost,

What is the difference between a cube and a SQL table since you mentioned that data stored in FAC2 and FACT table are in the cube, while data stored in the WB table is in the SQL table?

As an additional question, i assume that query will seek the appropriate values first in the WB table, before trying FAC2 and finally trying the FACT table. Can anyone add further explanations or correct me?

Cheers

[james_lim]

A cube can be consisted of several tables but BPC is using only 3 tables.

Each Tables represent a partition. As you know, FACT and FAC2 is MOLAP partition and WB is ROLAP partition. (ROLAP partition is mandatory for Real-time update feature BPC 5.X version)

Whenever user send data to server, BPC sending engine will read data from FACT, FAC2 and WB and will calculate difference. Those difference will be posted into WB table.

Therefore, if there is no difference between send data and previous data, any data will not be posted.

BPC is using intelligent Query engine which is called SQE (Shared Query Engine). If requested cells are base members, it will not ask data to cube because all data is in the SQL table so it will read all data from SQL table so that it can get better performance.

I hope it can be an answer.

Thank you.

James Lim.