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(ish) update

I'm updating every row in a 200mb table. I'm trying to work out why it
running so slowly and have found that it increases the size of the log file
by 625mb an then empties it, I believe, but leaves it at the same size. I've
got the recovery mode set to simple which I thought was not meant to use the
log file. Can someone tell me what's going on and if there's some method of
speeding this up.
Thanks
MichaelHi Michael,
Simple recovery mode still uses the log file during the transaction, but
then truncates it after the transaction. The log file is used during the
transaction such that the transaction can be rolled back, if necessary (e.g.
if it hits an error or if you cancel the update halfway through). To battle
transaction log growth during large transactions, many people split the
transactions into batches.
Please refer to the following search for many threads related to this topic:
http://groups.google.com/groups?as_...r />
=2005&saf
e=off
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
--
"Michael C" <mculley@.NOSPAMoptushome.com.au> wrote in message
news:evIv9OVzFHA.3152@.TK2MSFTNGP10.phx.gbl...
> I'm updating every row in a 200mb table. I'm trying to work out why it
> running so slowly and have found that it increases the size of the log
> file by 625mb an then empties it, I believe, but leaves it at the same
> size. I've got the recovery mode set to simple which I thought was not
> meant to use the log file. Can someone tell me what's going on and if
> there's some method of speeding this up.
> Thanks
> Michael
>|||Every transaction is logged--even if you use the simple recovery mode. That
way the transaction can be rolled back.
To speed up the update you need to split it into smaller chunks. If the
individual updates are small enough, they will fit into the transaction log
without making it grow. I'm assuming that the growth is probably why the
update is running so slow.
How do you split it up? It depends. If you are setting a column to a
specific value, then you can use something like:
SET ROWCOUNT 1000
AGAIN:
UPDATE tableName SET columnName = <newColumnValue> WHERE columnName !=
<newColumnValue>
IF @.@.ROWCOUNT > 0 GOTO AGAIN
SET ROWCOUNT 0
(Sometimes it's better to use > or < instead of != to speed things up even
more.)
There are many ways to do this, but I'd need a better description of what
you're trying to do and DDL for the table.
"Michael C" <mculley@.NOSPAMoptushome.com.au> wrote in message
news:evIv9OVzFHA.3152@.TK2MSFTNGP10.phx.gbl...
> I'm updating every row in a 200mb table. I'm trying to work out why it
> running so slowly and have found that it increases the size of the log
> file by 625mb an then empties it, I believe, but leaves it at the same
> size. I've got the recovery mode set to simple which I thought was not
> meant to use the log file. Can someone tell me what's going on and if
> there's some method of speeding this up.
> Thanks
> Michael
>|||Hi,
its better to update records in smaller chunk then deleting as whole limit
row / record size for deleting in 1000 or as you like , so that you can
control T-LOG file size
Refer following thread :
http://www.sql-server-performance.c...?TOPIC_ID=10706
;-)
Regards

Large Slowly Changing Dimension

Hello,
I'm somewhat new to data warehousing, and have some basic questions.
Let's say I have a fact table, named sales. The financial analyst
would like to be able to sort the sales information based on several
customer related attributes. Some of these attributes would include
zip code, age, and income. While I can create a customer dimension,
these attributes change over time.
The analysts are really only interested in these customer attributes
at the time of the sale. For example, they only care about where a
customer lived at the time of the sale, but not where they are living
now. Now I know there are different techniques for dealing with
changing dimensions, but I'm left with the impression that it would be
making things more complicated then necessary.
Would it not be best to just simply store this information in the
fact table itself? While I'm aware that fact tables are primarily used
to store numerical information, I don't see the advantage of putting
this information in a separate dimension.
Your input would be much appreciated.
Thanks,
AttilaHi,
You say the analysts are interested in the customer attributes at the time
of the sale. So these attributes will have to be stored in dimensions to be
able to analyse them using datawarehouse techniques. These must be slowly
changing. So when you add more salesfacts, e.g. with a zipcode that doesnt
exist in the zipcode dimension yet, you'll have to add it to the dimension.
The alternative is to drop and recreate all the facts and dimensions, but
that's no option if the customers move or the incomes change in the source
system.
Good luck...
Jeroen.
"Attila" <ThisIsAFakeAddress35@.hotmail.com> wrote in message
news:9516751f.0401290847.23e49471@.posting.google.com...

quote:

---

> Hello,
> I'm somewhat new to data warehousing, and have some basic questions.
> Let's say I have a fact table, named sales. The financial analyst
> would like to be able to sort the sales information based on several
> customer related attributes. Some of these attributes would include
> zip code, age, and income. While I can create a customer dimension,
> these attributes change over time.
> The analysts are really only interested in these customer attributes
> at the time of the sale. For example, they only care about where a
> customer lived at the time of the sale, but not where they are living
> now. Now I know there are different techniques for dealing with
> changing dimensions, but I'm left with the impression that it would be
> making things more complicated then necessary.
> Would it not be best to just simply store this information in the
> fact table itself? While I'm aware that fact tables are primarily used
> to store numerical information, I don't see the advantage of putting
> this information in a separate dimension.
> Your input would be much appreciated.
> Thanks,
> Attila

---

|||> So these attributes will have to be stored in dimensions to be
able to analyse them using datawarehouse techniques.
Can you elaborate on this part? What specifically will an analyst not
be able to do if I store the customer data in the fact table?
Thanks,
Attila
"Spike" <jeroenaNOSPAM@.hotmail.com> wrote in message news:<bvgf14$sjl$1@.reader08.wxs.nl>...[QUOTE]
> Hi,
> You say the analysts are interested in the customer attributes at the time
> of the sale. So these attributes will have to be stored in dimensions to b
e
> able to analyse them using datawarehouse techniques. These must be slowly
> changing. So when you add more salesfacts, e.g. with a zipcode that doesnt
> exist in the zipcode dimension yet, you'll have to add it to the dimension
.
> The alternative is to drop and recreate all the facts and dimensions, but
> that's no option if the customers move or the incomes change in the source
> system.
> Good luck...
> Jeroen.
> "Attila" <ThisIsAFakeAddress35@.hotmail.com> wrote in message
> news:9516751f.0401290847.23e49471@.posting.google.com...|||I suggest reading articles written by Ralph Kimball. Here's one of his
classic articles on slowly changing dimensions.
http://www.dbmsmag.com/9604d05.html
"Attila" <ThisIsAFakeAddress35@.hotmail.com> wrote in message
news:9516751f.0402020643.36d448b7@.posting.google.com...
> able to analyse them using datawarehouse techniques.
> Can you elaborate on this part? What specifically will an analyst not
> be able to do if I store the customer data in the fact table?
> Thanks,
> Attila
>
> "Spike" <jeroenaNOSPAM@.hotmail.com> wrote in message
news:<bvgf14$sjl$1@.reader08.wxs.nl>...
time
be
slowly
doesnt
dimension.
but
source|||There is an entire chapter in Ralph Kimballs book on mini dimensions,
which help to stabilise the pain of rapidly changing dimensions.
Ashish
On Tue, 17 Feb 2004 15:29:51 -0500, Domenico Discepola
<domenico_discepola@.quadrachemicals.com> wrote:

> I suggest reading articles written by Ralph Kimball. Here's one of his
> classic articles on slowly changing dimensions.
> http://www.dbmsmag.com/9604d05.html
>
> "Attila" <ThisIsAFakeAddress35@.hotmail.com> wrote in message
> news:9516751f.0402020643.36d448b7@.posting.google.com...
> news:<bvgf14$sjl$1@.reader08.wxs.nl>...
> time
> be
> slowly
> doesnt
> dimension.
> but
> source
>
Using M2, Opera's revolutionary e-mail client: http://www.opera.com/m2/