Question on Fact table and Optimization

[Former Member]

Dear Friends,

I have few question on BPC 7.5 MS version

1. When we write a EVDRE report, data comes from which table? i.e. Fact, Fac2 or FacWB.
2. How can I find out when table in DB, Cube in SSAS  and Application in BPC were refreshed or optimized last time
3. What is the SQL statement for finding out the total record in each of the above table.
4. For better performance, do I need to refresh or optimize all the three ( tables, cube, application) separately or just optimizing application in BPC will be enough.

Thanks,

Nisha

[former_member186498 · Accepted solution]

Hi Nisha,

just to add some information on point 4), the WB table is the most critical, depending from SQL version if there are more than 50.000-500.000 records the performance of the system fall down, so if you have packages that insert/update a lot of records, more than 50.000 records, instead of manually execute the optimize you should think to schedule, even every 30 minutes a lite optimize without checks, one incremental once a day in the night and a full optimize with checks (index and compress) once a week normally in  the week end night.

To practice with the bpc table, uou can use the Sql Server management studio to see the structures of the apssets you've create, if you know  a little bit of SQL language you can build query or with analysis services see the OLAP structure and write through a wizard "queries" on the OLAP to see the inserted data. If you don't know the SQL and MDX language just look at google and you will found a lot of documentation from Microsoft also.

Kind regards

     Roberto

[Former Member]

Hi Nisha,

1) Evdre determines based on the the data being requested. If all the dimensions are requested from base member level, evdre triggers an sql query to the sql tables - which is a combination of all 3 fact tables - fact, fac2, factWB. If you have any dimension at the parent level member or a calculated member, evdre triggers an MDX query to all three cube partitions in the OLAP - Fact,fac2 and FactWB.

2) You may check the log in Appserver DB in the TblLogs table. It will have details of cube processing time in BPC. For data audit, you may enable Data audit in BPC. But for detailed logging at the SQL level across all tables, you may try enabling the audit in DB and dig through the log file. But I really dont find any purpose of going through such enormous pain, when you have Data audit facility. You will have time logged on for the Data Manager package executions in the Package logs too.

3) The tables are named in tblfactWBApplication, tblfac2Application, tblfactApplication format. So, you can query these tables for each application in the Appset DB.

4) Optimization from BPC will take care of records movement in SQL level and cube process at OLAP level, by itself. So, nothing is needed to be done other than the front end optimization. The records movement and cube process, differs based on optimization techniques.

Karthik AJ