I have few question on BPC 7.5 MS version
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.
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.