OLAP – How it works | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

OLAP – How it works

Is this process flow normal?
We have a OLAP application which writes data to the write back table and at regular intervals – there’s a package which moves this data from Write back to Fac2 Table. On weekly bases – data from Fac2 gets moved to Fact Table.
Question is – which table does the Cube acess and populate it self, when front end users request data?
What happens if we fail to move from Fac2 to Fact table?
Appreciated any feedback.

One of the technet article refers:
A primary purpose of business intelligence is to provide useful, business-centric information for strategic planning. Microsoft® SQL Server™ 2000 Analysis Services supports strategic planning in many ways, one of which is by providing the capability to temporarily or permanently write values back to a set of cells within a cube. This capability, called cell writeback, supports strategic planning by facilitating interactive "what if" analyses. You can temporarily write values to a cube and examine the resulting aggregations locally, without actually changing data on the Analysis server. Once you have completed your planning activity and have a useful set of values, you can permanently write the values to the cube.
Permanently writing a value to a leaf cell in a cube requires an individual writeback operation, which writes a single record to a writeback table. However, permanently writing a value to a non-leaf cell can require hundreds of thousands of individual writeback operations, each of which writes a single record to a relational database. The value for a non-leaf cell must be allocated across all of the leaf cells that are subordinate to the non-leaf cell, and then the values of the leaf cells are aggregated to provide the value of the non-leaf cell. Because this process can require a high volume of individual transactions, permanent cell writeback operations can significantly affect the performance of the underlying relational database.
see http://sqlserver-qa.net/blogs/bi/ar…-2005-analysis-services-operations-guide.aspx for more informaton.

Thanks a much for providing this detail explanation. very helpful and gives much bigger picture now.
You are best.

One qiuck question: If the cube has to read the data from OLTP Database – what table would it refer to. after going through the above link – it seems – after changes are performed in write back – then the data be moved to another table (fac2) then from there it gets moved to Fact table. so if a cube has to read and populate itself – which table will get hit? Fact or Fac2?


Software Reviews | Book Reviews | FAQs | Tips | Articles | Performance Tuning | Audit | BI | Clustering | Developer | Reporting | DBA | ASP.NET Ado | Views tips | | Developer FAQs | Replication Tips | OS Tips | Misc Tips | Index Tuning Tips | Hints Tips | High Availability Tips | Hardware Tips | ETL Tips | Components Tips | Configuration Tips | App Dev Tips | OLAP Tips | Admin Tips | Software Reviews | Error | Clustering FAQs | Performance Tuning FAQs | DBA FAQs |