Last Run Time History

Is there a way to store the history of every subscription to a report?
Currently, it only stores the last run time.Presently no. You could write a custom delivery extension that logged this
information for each delivery but that is an involved process.
-Lukasz
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"jvmiller" <jvmiller@.discussions.microsoft.com> wrote in message
news:CFD19093-C5EE-4D9E-BC4F-0C4CBDE84D4A@.microsoft.com...
> Is there a way to store the history of every subscription to a report?
> Currently, it only stores the last run time.

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 XML File

I would like to create a history table that stores xml files. The xml files size are in the range of 300 kb to 600 kb. What is the best approach to do this? Any suggestion will be appreicated.
What do you want to do with these XML documents? Are you just looking for a
safe place to keep them or are you planning to use the database to search
the contents of the files? If they are history files, is it enough to be
able to retrieve them by date or do you need to be able to search through
the contents of the file? Do they follow a fixed schema or are they of
random structure?
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Abi" <anonymous@.discussions.microsoft.com> wrote in message
news:072255ED-F5A2-4C1E-B7AA-FACED3034D64@.microsoft.com...
>I would like to create a history table that stores xml files. The xml
>files size are in the range of 300 kb to 600 kb. What is the best approach
>to do this? Any suggestion will be appreicated.