We’re smarter together. Learn from this collection of community knowledge and add your expertise.

Using the best of delta updated DW tables and snapshot retrieval of data

by New Contributor bl_jyskebank_dk on ‎01-09-2015 01:49 AM - edited on ‎10-05-2015 03:58 PM by Community Manager (1,421 Views)

Did you ever want to be able to produce a lot of historic data for your users, and at the same time not have to administrate a huge amount of data?

 

Delta updated tables in your DW is a very effective way to preserve disk space, but not that easy for everybody to retrieve data from later on.

 

Snapshot data is a lot easier to access for the end user, whether it be the programmer or the analyst.

Snapshot data is every piece of data layed out every day, making it easy to pick all your data at a specific date.

It's redundant as hell and some database administrators are not that pleased with this approach.

 

That's why I took the best from both worlds and combined it into a tool that preserves space and makes it easy to retrieve your data.

 

It gives a little extra work on the data preparation side, but it's actually quite easy to handle.

 

In the attached Excel document there is a simple example of how to model your DW tables and create a snapshot view on top of them, making it easy to retrieve data based on specific dates.

 

The table CUSTOMER_BASE is the base table holding all the information about the customers.

It has an ID column that is unique for every record in the table.

The FROMDATE column tells us from what date the actual record apply.

It also has a column names SOCSECNO (social security number) with an index. This is used for selecting data in the example.

 

The table CUSTOMER_SNAP is the actual snapshot table, holding a reference to the base data for a customer, data that applies at the date found in the DATE column.

This table holds a record for each customer for each and every date up until now.

You have to make a small batch program/job that creates this table from the BASE table, and when adding data to the BASE table you have to make some housekeeping in the SNAP table.

 

On top of these two tables put a view that joins the table on the ID column.

Now your users can easily access data from a delta updated DW table using the snapshot access principle.

Comments
by Super User
on ‎01-09-2015 10:01 AM

Very nice post, powerful and simple!

A code sample for the creation of the snap table would make it perfect.

Have you tested this on large dimensions/tables? How is this helper table affecting performance?

by New Contributor bl_jyskebank_dk
on ‎01-10-2015 06:08 PM

I have not tested it on very large tables yet but will do so in the coming week. I will publish the results here. The test will include joins of views.

by Regular Contributor
on ‎01-13-2015 11:19 AM

I would really like to have access to the pdf but for some reason when I try to download the pdf files from this web site I only get a single page in the pdf file. Can I get the pdf from other place?

by Community Manager
on ‎01-13-2015 12:47 PM

I'm only seeing one page of the PDF too, CTorres .

bl@jyskebank.dk, is that the whole file?

Anna

by New Contributor bl_jyskebank_dk
on ‎01-14-2015 01:58 AM

There is only one page in the PDF. I had some problems using this forum with IE9, so switched to Firefox. It solved my problems using the forum. In the text I mentioned that an Excel file is attached. It is of course a PDF file. It has only one page. I'm conducting performance analysis right now to see how it performs when joining tables with millions of records. The test is carried out on Oracle.

by New Contributor bl_jyskebank_dk
on ‎01-14-2015 06:06 AM

When the test has finished I will publish results and sample code.

by Community Manager
on ‎01-14-2015 09:39 AM

OK, sounds good! Thanks for clarifying and posting your results when ready.

Anna

by New Contributor bl_jyskebank_dk
on ‎01-22-2015 03:20 PM

Got held up by some Oracle space problems and other activities. Cannot guarantee a date for the results, but I will be back as soon as the tests are finished. At that time I have some more information on how to potentially use this setup for a near-real-time Data Warehouse.

by New Contributor bl_jyskebank_dk
on ‎03-16-2015 05:37 AM

One step further...

We have conducted some performance tests on Oracle, and it shows okay performance.

We made a couple of huge Oracle tables and put views on top of them one-to-one, and put a joined view on top of them as well.

When opening an Oracle view directly from SAS Foundation in ViewTable it responds immediately showing the contents of the view.

When opening a joined Oracle view on the huge tables, it takes some minutes before it responds.

This is because Oracle has to join the tables and that takes time.

When doing SQL or datastep queries on the joined view it responds okay / fast when using a where clause.

No unexpected bad performance.

We tested on billions of records in the tables. Of course the Oracle server setup means something, but I have no details on that.

We are considering partitioning the tables in Oracle for even better performance, but for now we are not doing that.

Next step is implementing this setup in our Data Warehouse. We are currently working on that.

This prepares us for doing near-real-time updates in the Warehouse.

We could query (service requests) any changes in customers basic information every hour, and in return get a few hundred or thousand records.

We would then process these few records by inserting them directly into the delta table and adjust the time table accordingly.

The gives us the opportunity to hold historic data in the table setup.

Our programs can then access the data through the view using date specifications (where statements) or they can access the delta table directly if more advanced handling of data is necessary.

By using near-real-time updating we are transferring batch processing of large amounts of data at night to smaller amounts scattered over the day, thus eliminating the need for heavy batch processing at night. So we are potentially looking at reconfiguring BI servers because the work load will look different in the future.

More to come...

by Super User
on ‎03-24-2015 03:41 AM

Thanks for the update!

Your turn
Sign In!

Want to write an article? Sign in with your profile.


Looking for the Ask the Expert series? Find it in its new home: communities.sas.com/askexpert.