Question on Fact table and Optimization
I have few question on BPC 7.5 MS version
- When we write a EVDRE report, data comes from which table? i.e. Fact, Fac2 or FacWB.
- How can I find out when table in DB, Cube in SSAS and Application in BPC were refreshed or optimized last time
- What is the SQL statement for finding out the total record in each of the above table.
- 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.
Roberto Vidotti replied
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.