Showing posts with label current. Show all posts
Showing posts with label current. Show all posts

Sunday, March 25, 2012

dimension loading best practice?

It has been suggested that when loading dimensions, the first step should be to clear the current dimension table and reload in total from the data source. I use integer identity PK fields in all my dimension tables.

When loading my fact table I'm only loading rows that have been added or changed since the last SSIS run.

Wouldn't this introduce the possibility of mis-matched key relationships if, for some reason, the order of the rows in the dimension table changed and a fact row that doesn't get modified in this SSIS run pointed to a surrogate key in a dimension table that was changed?

I suppose in most cases a change in the OLTP that caused a reordering of the dimension table - say a customer was dropped - would show up as a modification in the fact load and cause the row to be updated. However if someone changed the indexing in the OLTP it might cause the customer order to chagne causing them to be loaded in a different sequence with different surrogate keys in the dimension table.

It just concerns me that a possibility for corruption seems to exist in this type of scenerio. It seems like one should load the dimensions and facts in the same manner.

Who suggests truncating dimensions before loading them? This is not good practice. New rows should be inserted and existing rows should be updated. If you need to track changes (Slowly Changing Dimension), then you should use the SCD wizard inside SSIS.|||

John:

I am a student of the Kimball method of data warehousing. In most of my experience I have never completely rebuilt a dimension during each update, for precisely the same reason you mention. In my experience most changes in dimensions are handled using the Slowly Changing Dimension process, and SSIS provides a very elegant tool to do this for you.

I am not sure what your fact is, but unless you have scheduled a regular roll-out of data from your warehouse, say you only wanted to maintain a specific time period of data in your warehouse, you should not really be deleting rows from your dimension. If you have a very large dimension that you wish to purge occassionally I suggest that you devise a process that will account for the fact relationships that exist with the dimension records that are marked for deletion.

On a regular basis though it is not typical to completely refresh dimensions during each update.

|||

Thanks! I thought it came from the Kimball video posted here but I could have misunderstood something I saw on the screen that was being used for something else or it might have been from one of my books. It seemed too easy to be workable. However it's not that big a deal to treat dimension loading the same as facts.

I'm lucky in that I don't have any slowly changing data (yet) so I can treat all my modifications as simple updates. And every row in my OLTP has a CreateDate, ChangeDate, DeleteDate attached to it.

|||The only thing I truncate in my data warehouses are aggregated fact tables. The detail fact tables undergo inserts only, while aggregates (monthly data, for instance) are just roll ups of the detail tables. Instances like this are perfect for truncations since they need to be rebuilt every night.|||

Now that I'm looking at incremental updates of my dimensions I forsee another problem.

My sales date comes from the same OLTP table as most of my facts. I don't have a separte date table. So to load the date dimension I'm going to read the same rows as I would for loading my fact table. My OLTP row will alert me that the row has been modified but NOT whether the date column has been modified.

Also, I assume I'm going to be picking up several thousand sales each day so there will be several duplications. Is this why some designs, including some from microsoft, leave the date field in the fact table and let SSAS generate date dimensions from there?

|||

You do not need to have a data source of a Time Period table. You can very simply create a time period dimension using SSIS or a stored procedure for a time period that spans the entire history and then several years into the future, and the appropriate grain of your fact.

Again, it is typical that you have a very robust Time Period dimension that has all the attributes users would need to slice the data along.

|||

John:

I suggest you start a new thread on this topic of Time Dimension as you have already marked this post as being answered. This way others will look at the post.

Monday, March 19, 2012

Differential Restore

Win2K3 SQL2K Enterprise Edition
This is my current backup schedule
Sunday FULL
M-Sat Differential
TranLogs Backups every hour
I am trying to restore to a point in time, specifically to the time
right after my last Differentail backup. I am using Ent Manager for
this. I have restored the Full with no recovery and then I select just
the last Differential Backup for restore and I get the message
Cannot apply the backup on device 'd:\abcd\diff' to database 'TEST'
restore database is terminated abnormally
Is there something wrong with my restore process. This is a qa
environment that I am testing this on. I have added a lot of data to
the database and then manual run the diffential job. Now I am trying
to restore to this point but I keep getting this error. Any help would
be appreciated.Please check the backup file you are using.
Thanks
Ajay Rengunthwar|||Ajay, Not sure what you mean exactly. I was able to restore my last
full backup. Both with and without recovery. I am just having trouble
restoring the differentials backup after that.

Differential Restore

Win2K3 SQL2K Enterprise Edition
This is my current backup schedule
Sunday FULL
M-Sat Differential
TranLogs Backups every hour
I am trying to restore to a point in time, specifically to the time
right after my last Differentail backup. I am using Ent Manager for
this. I have restored the Full with no recovery and then I select just
the last Differential Backup for restore and I get the message
Cannot apply the backup on device 'd:\abcd\diff' to database 'TEST'
restore database is terminated abnormally
Is there something wrong with my restore process. This is a qa
environment that I am testing this on. I have added a lot of data to
the database and then manual run the diffential job. Now I am trying
to restore to this point but I keep getting this error. Any help would
be appreciated.Please check the backup file you are using.
Thanks
Ajay Rengunthwar|||Ajay, Not sure what you mean exactly. I was able to restore my last
full backup. Both with and without recovery. I am just having trouble
restoring the differentials backup after that.

Friday, February 24, 2012

different image source for online and PDF reports?

In my current job, I've inherited a website and some ReportViewer
reports, and I've figured out how to poke around and make some changes
requested by my employer, but there's still a lot that I don't
understand. Any input would be appreciated.
Here's the current problem: the reports that we make available on our
website are mostly made up of images. Up until now, we've been saving
the images at the size that they need to be when the reports are
viewed online at 100%. Of course, this means that when exporting the
report to PDF, or printing it, the quality is unacceptable. I've tried
saving larger images and scaling them proportionately in the reports,
which results in beautiful PDFs and illegible online reports due to
resampling. Naturally, and reasonably, my employer wants our reports
to look good online AND when they're printed. I've looked around
online and found that other people have wrestled with this problem,
with no clear solutions.
The one idea that I've come up with is to use different images for the
online and printed versions (so the online report would use /preview/
image01.png and the PDF would use /print/image01.png or something). Do
you think this is possible? Any idea where I would start?
Thanks in advance for any advice.
SarahOn Jun 4, 2:37 pm, "s. o." <sarah.oh.li...@.gmail.com> wrote:
> In my current job, I've inherited a website and some ReportViewer
> reports, and I've figured out how to poke around and make some changes
> requested by my employer, but there's still a lot that I don't
> understand. Any input would be appreciated.
> Here's the current problem: the reports that we make available on our
> website are mostly made up of images. Up until now, we've been saving
> the images at the size that they need to be when the reports are
> viewed online at 100%. Of course, this means that when exporting the
> report to PDF, or printing it, the quality is unacceptable. I've tried
> saving larger images and scaling them proportionately in the reports,
> which results in beautiful PDFs and illegible online reports due to
> resampling. Naturally, and reasonably, my employer wants our reports
> to look good online AND when they're printed. I've looked around
> online and found that other people have wrestled with this problem,
> with no clear solutions.
> The one idea that I've come up with is to use different images for the
> online and printed versions (so the online report would use /preview/
> image01.png and the PDF would use /print/image01.png or something). Do
> you think this is possible? Any idea where I would start?
> Thanks in advance for any advice.
> Sarah
That sounds like the best option. You should have one report that can
be viewed and then when the user goes to export it, change the report
behind the scenes in the application to point to the smaller imaged
report. This should be fairly straight forward in terms of pointing to
one report in a report viewer and then pointing to another report once
the user selects something like a print preview aspx page or
something. Hope this helps.
Regards,
Enrique Martinez
Sr. Software Consultant