ROLAP Polling Query - use a change tracking table

Setting up the polling query for our real-time ROLAP cube against an EXASOL database, we used an easy, but very bad practice approach.  We set our polling query directly against a fact table. Nice and easy for testing and proof of concept. Production? don't consider using it.

Polling a count against a small or medium size fact table might won't create much of a problem. When the dataset is huge it might take more resources.  So, when the data set is really large, and our goal is very near real time, why not poll a very small table.

Data Change Table

Let's call it TrackDataChange.  A minimalist version will have just a few columns:  the source system that loaded the data, the load date and a primary key ID. The only difficult part is you have to configure your ETL process(es) to insert a single row to your new TrackDataChange table. The new record simply marks that the ETL process has completed.

With the TrackDataChange table in place, and your ETL processes configured to insert a row after inserting new fact table table, your polling query will look like this:

        SELECT COUNT(*) FROM DWTEST.TRACKDATACHANGE;

You'll probably want more than just the basic columns. The key point is to run SSAS's polling query against an independent table dedicated to tracking fact table changes.

Pros

the polling query avoids data tables used for data analysis

the TrackDataChange table is narrow and holds few records

the TrackDataChange table is quick to query

the TrackDataChange table is quick to insert into

Cons

the ETL process is marginally more complex

Data Linage - Credibility for ETL History

Lack of credibility is perhaps the biggest problem with data warehouses. So now I'm noticing some tools to help people trace data in a data warehouse back to the source. Where it came from, how it was changed, etc.  - all this is after-the-fact. Perhaps valuable, but a well designed ETL process already includes this type of information. A result from the premise that the data in a data warehouse is 100% accurate. It rarely has been, but it should be close.

Big data, Hadoop, web searches, and much of the on-going world of statistics are approximations. Best guesses  -  that can change once you make an adjustment to the incoming data or the rules used for the query.