Updating a table with warehouse

Question

Updating a table with warehouse

Terry_Pino

Ten Centuries

Points: 1253
More actions
August 11, 2004 at 12:43 am

#59308

Hi All,
I have a table that contains payrun data. I need to populate this table from data in archived databases, plus any new data in the production database. Since we are talking a lot of records, ideally I dont want to have to reload data from the archive databases over and over. I just want new data from production. If anyone has any suggestions Id like to hear them.

Regards,
Terry

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Jonathan Stokes SSCrazy Eights Points: 9861 More actions · Answer 1

If you set up an olap db you can achieve this. When you populate the warehouse you can choose the option to append data only to the fact table.

However, this will not reflect changes or deletions. It will only add new records.

------------------------------
The Users are always right - when I'm not wrong!

Terry_Pino Ten Centuries Points: 1253 More actions · Answer 2

Hi,

Sorry if the following question seem a bit dumb...Im still trying to work out all the bits to a warehouse/olap environment. My questions are:

1. How do you create an OLAP database

2. Do you neccessarily need Analysis services if you are not intending to create cubes at this stage?

3. Do you need a particular version of SQL server for Analysis services?

4. Can you install Analysis services for a particular instance of SQL server rather than all instances and is this a good idea?

Thats just the startIf you have any good reading to recommend that tells you in a step by step approach what to do, please do so. I find the books on line to jump all over the place and not be very useful unless you know whats going on.

Regards,

Terry Pino

conrad coetzee SSC Enthusiast Points: 112 More actions · Answer 3

It depends what the data looks like in your source database, in our Warehouse I reload by checking certain fields ie Transactionnumbers or Date keys,

Declare @Tnumber int

Set @Tnumber = (Select max(transactionnumber)

From Warehouse table) (set up as linked server)

Select Data

From Source database

Where transactionnumber > @Tnumber or @MaxDate

Or you can use the checksum function. Just remember checksum function will only work if you use it with another unique key like Customercode or Transactionnumbers.

Jonathan Stokes SSCrazy Eights Points: 9861 More actions · Answer 4

A basic overview of olap.

You need to have analysis services installed. I do not know which cd this comes on I am afraid.

You set up a db with a central table (called fact) that contains foreign keys to other tables that contain dimensions. I.E. Product, Company etc. A typical db will be a star schema.

Fill up the tables with normal insert statements or better yet use dts.

You then use analysis services to generate an olap cube. You can then schedule a job to regenerate the cube looking at the change of data in the table.

For details on what cubes are about and how to populate see this series of articles - http://www.informit.com/articles/article.asp?p=24019&seqNum=3

You will also get some ideas if you choose not to use olap.

For anlaysis services generation of olap cubes there is a greate hands on demo available when you install analysis services.

------------------------------
The Users are always right - when I'm not wrong!

Terry_Pino Ten Centuries Points: 1253 More actions · Answer 5

Very enlightening Jonathan. Thank you. Just a comment that I have; 'is it neccessary to truncate the log if you are using the simple recovery mode?'

I'm not exactly sure at this stage if I am looking to implement a data warehouse as it is defined. Essentially, I am looking to build a database optimised for reporting purposes. In the context of our organisation, I am looking to use MS Reporting services to generate reports off the payroll system. So my plan off attack has been to build a database with all the relevant data pulled in from the different source tables and put into simplified tables. Whether this constitues a data warehouse or not is still a mystery to me. Im not sure how relevant Fact and Dimension tables are.

Regards,

Terry Pino

conrad coetzee SSC Enthusiast Points: 112 More actions · Answer 6

"A Data Warehouse is a repository of integrated information, available for queries and analysis. Data and information are extracted from heterogeneous sources as they are generated....This makes it much easier and more efficient to run queries over data that originally came from different sources."

http://www.datawarehouse.com

http://www.ralphcimball.com

regards

Conrad