Last image of Historical Data

Hello,

I have a slowly changing dimension Sales Person of type 2, where I am keeping history of the sales person Unit, Region, and Sales Channel which are attributed on the Sales Person dimension.

The client is requesting to be able to report on the Sales Person last image, for example if the last image Region of this sales person is Region X, then all the portfolio of this Sales Person should now be linked to Region X irrespective of what it was historically. At the same time, we should be able to report on the production done by region as it occured historically.

To my kowledge, my design would allow to report on production historically but not the last image. I read about an article that suggested to create additional attributes in the dimension (i.e. Current_Region, Current_Unit, ....) and thus for each row I will have two values for each historical attribute. Then, in order to produce the report for last image i will use the Current_ attributes that are being updated whenever a new image arrives.

I am not sure if this is a proper solution to duplicate the columns.

Would be glad to hear out any suggestions and opinions regarding this design issue.

Thanks in advance,

Grace

Hi! This is a common problem in data warehouse design and known as the slowly changing dimension(SCD) problem.

The approaches to solve this is normally outside of SSAS2005 and an ETL/DW problem:

-Type one: Overwrite all attributes/columns with the current value (This is the one you are suggesting)

-Type two: Put a timestamp on dimension members when a an attribute changes, that you would like to keep an history of. You will have to use surrogate keys for each member and version of the dimension keys to use this approach.

-Type tree: Add a new column for the attribute that you would like to keep history for.

The better approach is to separate attributes in different dimensions tables that have a dynamic relation and enter a new dimension key in the fact table. Separate geography from customer. Never have a key account or salesperson attribute in your customer table, because these relations change over time.

Have a look at www.kimballgroup.com for more information.

HTH

Thomas Ivarsson

Last image of Historical Data

Hello,

I have a slowly changing dimension Sales Person of type 2, where I am keeping history of the sales person Unit, Region, and Sales Channel which are attributed on the Sales Person dimension.

The client is requesting to be able to report on the Sales Person last image, for example if the last image Region of this sales person is Region X, then all the portfolio of this Sales Person should now be linked to Region X irrespective of what it was historically. At the same time, we should be able to report on the production done by region as it occured historically.

To my kowledge, my design would allow to report on production historically but not the last image. I read about an article that suggested to create additional attributes in the dimension (i.e. Current_Region, Current_Unit, ....) and thus for each row I will have two values for each historical attribute. Then, in order to produce the report for last image i will use the Current_ attributes that are being updated whenever a new image arrives.

I am not sure if this is a proper solution to duplicate the columns.

Would be glad to hear out any suggestions and opinions regarding this design issue.

Thanks in advance,

Grace

Hi! This is a common problem in data warehouse design and known as the slowly changing dimension(SCD) problem.

The approaches to solve this is normally outside of SSAS2005 and an ETL/DW problem:

-Type one: Overwrite all attributes/columns with the current value (This is the one you are suggesting)

-Type two: Put a timestamp on dimension members when a an attribute changes, that you would like to keep an history of. You will have to use surrogate keys for each member and version of the dimension keys to use this approach.

-Type tree: Add a new column for the attribute that you would like to keep history for.

The better approach is to separate attributes in different dimensions tables that have a dynamic relation and enter a new dimension key in the fact table. Separate geography from customer. Never have a key account or salesperson attribute in your customer table, because these relations change over time.

Have a look at www.kimballgroup.com for more information.

HTH

Thomas Ivarsson

Large Transaction Logs

I have some databases that are keeping log files (.ldf) that are 3 times the
size of the db and very large. What is the best way to shrink the size of
these .ldf files? I have made backups of the db's and transaction logs
using SQL database maintenance (as some have suggested), but this does not
shrink the .ldf files. What else can I do?
Brandon
Presentations Direct - "Document Finishing Solutions"
http://www.presentationsdirect.comBrandon,
Backing up a transaction log will clear it but not shrink it. Use DBCC
SHRINKFILE on the t-log. Tibor has some more info here:
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
HTH
Jerry
"Brandon" <bsmith@.presentationsdirect.nospam.com> wrote in message
news:eO5MHFOgGHA.1320@.TK2MSFTNGP04.phx.gbl...
>I have some databases that are keeping log files (.ldf) that are 3 times
>the size of the db and very large. What is the best way to shrink the size
>of these .ldf files? I have made backups of the db's and transaction logs
>using SQL database maintenance (as some have suggested), but this does not
>shrink the .ldf files. What else can I do?
> --
> Brandon
> Presentations Direct - "Document Finishing Solutions"
> http://www.presentationsdirect.com
>|||You might want to investigate what is or has inflated them.
Reindexing, bulk loads of data, replication, etc, can all grow your
logs and there are ways of addressing each.|||Logs can get "locked" if something is hitting them. Example: I have an
Indexdefrag job that fills up a log. I have to stop the job before I can
shrink it. (I am using Simple recovery mode on this database.) You may wan
t
to use View/Textpad to see if the log is filled with active or not. If
you're in full mode, after you backup the log, you should see the empty spac
e
increase and the active space decrease.
"Brandon" wrote:

> I have some databases that are keeping log files (.ldf) that are 3 times t
he
> size of the db and very large. What is the best way to shrink the size of
> these .ldf files? I have made backups of the db's and transaction logs
> using SQL database maintenance (as some have suggested), but this does not
> shrink the .ldf files. What else can I do?
> --
> Brandon
> Presentations Direct - "Document Finishing Solutions"
> http://www.presentationsdirect.com
>
>|||Specific to Transaction logs, here are a few articles you might like to
look at:
INF: Transaction Log Grows Unexpectedly or Becomes Full on SQL Server
http://support.microsoft.com/?id=317375
INF: Shrinking the Transaction Log in SQL Server 2000 with DBCC SHRINKFILE
http://support.microsoft.com/defaul...kb;EN-US;272318
INF: How to Shrink the SQL Server 7.0 Transaction Log
http://support.microsoft.com/defaul...kb;EN-US;256650
INF: Considerations for Autogrow and Autoshrink Configuration
http://support.microsoft.com/defaul...kb;EN-US;315512
INF: Incomplete Transaction May Hold Large Number of Locks and Cause
Blocking
http://support.microsoft.com/defaul...kb;EN-US;295108
Taking a backup of the log will not shrink the size of log , also its
important to consider the recovery model of the database. Consider using
DBCC SHRINKFILE.
Hope This Helps
Vishal Gandhi

Large Transaction Logs

I have some databases that are keeping log files (.ldf) that are 3 times the
size of the db and very large. What is the best way to shrink the size of
these .ldf files? I have made backups of the db's and transaction logs
using SQL database maintenance (as some have suggested), but this does not
shrink the .ldf files. What else can I do?
--
Brandon
Presentations Direct - "Document Finishing Solutions"
http://www.presentationsdirect.comBrandon,
Backing up a transaction log will clear it but not shrink it. Use DBCC
SHRINKFILE on the t-log. Tibor has some more info here:
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
HTH
Jerry
"Brandon" <bsmith@.presentationsdirect.nospam.com> wrote in message
news:eO5MHFOgGHA.1320@.TK2MSFTNGP04.phx.gbl...
>I have some databases that are keeping log files (.ldf) that are 3 times
>the size of the db and very large. What is the best way to shrink the size
>of these .ldf files? I have made backups of the db's and transaction logs
>using SQL database maintenance (as some have suggested), but this does not
>shrink the .ldf files. What else can I do?
> --
> Brandon
> Presentations Direct - "Document Finishing Solutions"
> http://www.presentationsdirect.com
>|||You might want to investigate what is or has inflated them.
Reindexing, bulk loads of data, replication, etc, can all grow your
logs and there are ways of addressing each.|||Logs can get "locked" if something is hitting them. Example: I have an
Indexdefrag job that fills up a log. I have to stop the job before I can
shrink it. (I am using Simple recovery mode on this database.) You may want
to use View/Textpad to see if the log is filled with active or not. If
you're in full mode, after you backup the log, you should see the empty space
increase and the active space decrease.
"Brandon" wrote:
> I have some databases that are keeping log files (.ldf) that are 3 times the
> size of the db and very large. What is the best way to shrink the size of
> these .ldf files? I have made backups of the db's and transaction logs
> using SQL database maintenance (as some have suggested), but this does not
> shrink the .ldf files. What else can I do?
> --
> Brandon
> Presentations Direct - "Document Finishing Solutions"
> http://www.presentationsdirect.com
>
>|||Specific to Transaction logs, here are a few articles you might like to
look at:
INF: Transaction Log Grows Unexpectedly or Becomes Full on SQL Server
http://support.microsoft.com/?id=317375
INF: Shrinking the Transaction Log in SQL Server 2000 with DBCC SHRINKFILE
http://support.microsoft.com/default.aspx?scid=kb;EN-US;272318
INF: How to Shrink the SQL Server 7.0 Transaction Log
http://support.microsoft.com/default.aspx?scid=kb;EN-US;256650
INF: Considerations for Autogrow and Autoshrink Configuration
http://support.microsoft.com/default.aspx?scid=kb;EN-US;315512
INF: Incomplete Transaction May Hold Large Number of Locks and Cause
Blocking
http://support.microsoft.com/default.aspx?scid=kb;EN-US;295108
Taking a backup of the log will not shrink the size of log , also its
important to consider the recovery model of the database. Consider using
DBCC SHRINKFILE.
Hope This Helps
Vishal Gandhi