I'm having a problem with the SetCurrentDay DTS package
of the BI Accelerator.
I'm loading a set of data using text files therefore I'm using
Master_Import.dts and Master_Update.dts unchanged. Then I run the VBScripts that are alsgo generated by Accelerator (VAMPanorama1_Load_VAMPanorama.vbs).
This loads the data from text files into the Staging Database and SM Database, and also runs the Set Current Day package with a fixed date of "2001-12-31".
The problem is data for the year of the Current Date is multiplied by a factor of six. Now, when I run SetCurrentDay Package Alone, to try to set a new date (say "2003-07-01") either opening the package in Enterprise Manager and changing the global variable, or using DTSRUN utility I get the same problem. The data es multiplied by a factor of 6 for the year 2003 and data for 2002 and 2001 shows up correctly at the year, qtr and month level.
I tried this on the Retail Analysis sample project that comes with Accelerator and had exactly the same problem.
Has anoyone seen something like this? am I doing something wrong here?
Any help is appreciated,I assume you've done something else by now, but I had the same problem. After verifying that the proper number of facts were loaded, and that a join wasn't creating additional records, I stumbled on the solution: There is no problem with the cube.
The problem is that the auto-generated base time dimensions each have an 'All' level and they should not. Setting all of them (for me they are Time.Standard and Week.Standard) to 'Current' will fix the problem and show the proper number of transactions. The reason they are multiplied is that 'All' aggregates across all time periods including the actual periods and the calculated ones like Current, Last Year, Change from Last, etc.
Originally posted by rsada
I'm having a problem with the SetCurrentDay DTS package
of the BI Accelerator.
I'm loading a set of data using text files therefore I'm using
Master_Import.dts and Master_Update.dts unchanged. Then I run the VBScripts that are alsgo generated by Accelerator (VAMPanorama1_Load_VAMPanorama.vbs).
This loads the data from text files into the Staging Database and SM Database, and also runs the Set Current Day package with a fixed date of "2001-12-31".
The problem is data for the year of the Current Date is multiplied by a factor of six. Now, when I run SetCurrentDay Package Alone, to try to set a new date (say "2003-07-01") either opening the package in Enterprise Manager and changing the global variable, or using DTSRUN utility I get the same problem. The data es multiplied by a factor of 6 for the year 2003 and data for 2002 and 2001 shows up correctly at the year, qtr and month level.
I tried this on the Retail Analysis sample project that comes with Accelerator and had exactly the same problem.
Has anoyone seen something like this? am I doing something wrong here?
Any help is appreciated,|||It's been some time since I last saw this thing. But could you refresh my memery
What do you mean by "Setting all of them (for me they are Time.Standard and Week.Standard) to 'Current'"? . Is this changing the name of the All Member or removing the All member ?
thanks|||It's actually constraining the dates to 'Current' but I found the real source of the problem.
The auto-generated date dimensions have custom members built into the dimension table, and one of them is called 'Current'. My original recommendation was that you could not leave the date dimensions set to 'All' (meaning that they are unconstrained) because that caused the totals to be much higher than they should be. Setting them to 'Current' or to some other specific value (a particular year, month, etc.) would fix the totals.
Here's the real problem: the unary operator property is not set by the spreadsheet generator. If you go into Analysis Manager and edit the Time.Standard dimension, you can fix it. For each level, look at the advanced properties and click the ellipse button beside the value for Unary Operators. A dialog comes up that lets you check "Enable Unary Operators" and tell it there is an existing field of <level name>_Oper (e.g. Year_Oper). This one change (setting it for all levels) fixes all of the auto-generated date dimensions.
What's happening is that without the unary operator, calculated dates like Current, Previous, Change, %Change, etc. are treated just like any other year value (like 2004) so the cube adds together all of the values causing data to be added both for the actual date that it occurs, and to the calculated dates (current, etc.). The unary operator lets the value in <level name>_Oper determine if the values will aggregate or not, and thankfully, the attribute has the right values.|||Thanks a lot Barclay I got it workin fine now..!! :)
Showing posts with label loading. Show all posts
Showing posts with label loading. Show all posts
Thursday, March 8, 2012
Saturday, February 25, 2012
better disk config for staging & tempdb files...
Hi,
I want to have your feedback on how to configure my drives to insure a good
performance during loading process & transformation against a staging
database.
Image you have 4 drives available for the staging database & tempdb
database...
loosing these disk is not important, so no redundancy required.
I read from an external database into the staging, I do some updates and
copy into the staging himself, and finally I read the staging to load the
datawarehouse.
I execute more then 70 DTS packages, the sequence of these packages is
optimized. So I could write a fact table in the staging while I read another
to fill the datawarehouse at the same time.
I have 4 "big" fact tables (from 1 millions of rows to 20 millions)
There is "only" 4Gb to 5Gb used by the staging database during the loading
process.
how to configure these disks?
Does it better to setup all disks in raid 0? (the system do everything)
Does it better to keep the 4 disks separatly, and create multiple files &
file groups and dedicating 1 disk by fact table?
Creating 1 filegroup and 4 files in this file group (1 on each disk)? (SQL
Server manage the sharing usage of the disks)
Where to put log files?
thanks for your feedback.
Jerome.Jerome,
What's the cost to the business if your DW is down for several days? Having
nonredundant disk for tempdb is generally unacceptable. Having nonredundant
disk for staging is a bit unusual. Push back to get more disk.
If I were placed in this situation and could not get more disk, I'd mirror
and strip with the controller and say that's all the performance she's got.
If management and the business owners are ready to signoff on the potential
of several days down time. The amount of down time depends on outside
hardware availability and support staff availability. Put your logs for
tempdb and staging on one disk. Use the disk controller to strip the other
three and place one file per processor (create all the same size) within the
file group. Normally I would break it out more but four disks isn't enough
to do much with.
"Jj" <willgart@.BBBhotmailAAA.com> wrote in message
news:eT0XnuEfFHA.3692@.TK2MSFTNGP09.phx.gbl...
> Hi,
> I want to have your feedback on how to configure my drives to insure a
> good performance during loading process & transformation against a staging
> database.
> Image you have 4 drives available for the staging database & tempdb
> database...
> loosing these disk is not important, so no redundancy required.
> I read from an external database into the staging, I do some updates and
> copy into the staging himself, and finally I read the staging to load the
> datawarehouse.
> I execute more then 70 DTS packages, the sequence of these packages is
> optimized. So I could write a fact table in the staging while I read
> another to fill the datawarehouse at the same time.
> I have 4 "big" fact tables (from 1 millions of rows to 20 millions)
> There is "only" 4Gb to 5Gb used by the staging database during the loading
> process.
> how to configure these disks?
> Does it better to setup all disks in raid 0? (the system do everything)
> Does it better to keep the 4 disks separatly, and create multiple files &
> file groups and dedicating 1 disk by fact table?
> Creating 1 filegroup and 4 files in this file group (1 on each disk)? (SQL
> Server manage the sharing usage of the disks)
> Where to put log files?
> thanks for your feedback.
> Jerome.
>|||the cost is 0 if we can't load data.
a downtime of 4hour to change a disk if we don't have one in the office is
not an issue.
I don't understand why having the tempdb database on a non-fault taulerant
disk is not a good idea from your point of view!!!
tempdb is the only database recreated when er restart SQL server, so the
downtime in case of a crash is ... 5 minutes?
Thanks for the guide about 1 disk by processor.
I'll plan this config.
"Danny" <someone@.nowhere.com> wrote in message
news:1oRwe.14848$Xr6.6230@.trnddc07...
> Jerome,
> What's the cost to the business if your DW is down for several days?
> Having nonredundant disk for tempdb is generally unacceptable. Having
> nonredundant disk for staging is a bit unusual. Push back to get more
> disk.
> If I were placed in this situation and could not get more disk, I'd mirror
> and strip with the controller and say that's all the performance she's
> got.
> If management and the business owners are ready to signoff on the
> potential of several days down time. The amount of down time depends on
> outside hardware availability and support staff availability. Put your
> logs for tempdb and staging on one disk. Use the disk controller to strip
> the other three and place one file per processor (create all the same
> size) within the file group. Normally I would break it out more but four
> disks isn't enough to do much with.
> "Jj" <willgart@.BBBhotmailAAA.com> wrote in message
> news:eT0XnuEfFHA.3692@.TK2MSFTNGP09.phx.gbl...
>
I want to have your feedback on how to configure my drives to insure a good
performance during loading process & transformation against a staging
database.
Image you have 4 drives available for the staging database & tempdb
database...
loosing these disk is not important, so no redundancy required.
I read from an external database into the staging, I do some updates and
copy into the staging himself, and finally I read the staging to load the
datawarehouse.
I execute more then 70 DTS packages, the sequence of these packages is
optimized. So I could write a fact table in the staging while I read another
to fill the datawarehouse at the same time.
I have 4 "big" fact tables (from 1 millions of rows to 20 millions)
There is "only" 4Gb to 5Gb used by the staging database during the loading
process.
how to configure these disks?
Does it better to setup all disks in raid 0? (the system do everything)
Does it better to keep the 4 disks separatly, and create multiple files &
file groups and dedicating 1 disk by fact table?
Creating 1 filegroup and 4 files in this file group (1 on each disk)? (SQL
Server manage the sharing usage of the disks)
Where to put log files?
thanks for your feedback.
Jerome.Jerome,
What's the cost to the business if your DW is down for several days? Having
nonredundant disk for tempdb is generally unacceptable. Having nonredundant
disk for staging is a bit unusual. Push back to get more disk.
If I were placed in this situation and could not get more disk, I'd mirror
and strip with the controller and say that's all the performance she's got.
If management and the business owners are ready to signoff on the potential
of several days down time. The amount of down time depends on outside
hardware availability and support staff availability. Put your logs for
tempdb and staging on one disk. Use the disk controller to strip the other
three and place one file per processor (create all the same size) within the
file group. Normally I would break it out more but four disks isn't enough
to do much with.
"Jj" <willgart@.BBBhotmailAAA.com> wrote in message
news:eT0XnuEfFHA.3692@.TK2MSFTNGP09.phx.gbl...
> Hi,
> I want to have your feedback on how to configure my drives to insure a
> good performance during loading process & transformation against a staging
> database.
> Image you have 4 drives available for the staging database & tempdb
> database...
> loosing these disk is not important, so no redundancy required.
> I read from an external database into the staging, I do some updates and
> copy into the staging himself, and finally I read the staging to load the
> datawarehouse.
> I execute more then 70 DTS packages, the sequence of these packages is
> optimized. So I could write a fact table in the staging while I read
> another to fill the datawarehouse at the same time.
> I have 4 "big" fact tables (from 1 millions of rows to 20 millions)
> There is "only" 4Gb to 5Gb used by the staging database during the loading
> process.
> how to configure these disks?
> Does it better to setup all disks in raid 0? (the system do everything)
> Does it better to keep the 4 disks separatly, and create multiple files &
> file groups and dedicating 1 disk by fact table?
> Creating 1 filegroup and 4 files in this file group (1 on each disk)? (SQL
> Server manage the sharing usage of the disks)
> Where to put log files?
> thanks for your feedback.
> Jerome.
>|||the cost is 0 if we can't load data.
a downtime of 4hour to change a disk if we don't have one in the office is
not an issue.
I don't understand why having the tempdb database on a non-fault taulerant
disk is not a good idea from your point of view!!!
tempdb is the only database recreated when er restart SQL server, so the
downtime in case of a crash is ... 5 minutes?
Thanks for the guide about 1 disk by processor.
I'll plan this config.
"Danny" <someone@.nowhere.com> wrote in message
news:1oRwe.14848$Xr6.6230@.trnddc07...
> Jerome,
> What's the cost to the business if your DW is down for several days?
> Having nonredundant disk for tempdb is generally unacceptable. Having
> nonredundant disk for staging is a bit unusual. Push back to get more
> disk.
> If I were placed in this situation and could not get more disk, I'd mirror
> and strip with the controller and say that's all the performance she's
> got.
> If management and the business owners are ready to signoff on the
> potential of several days down time. The amount of down time depends on
> outside hardware availability and support staff availability. Put your
> logs for tempdb and staging on one disk. Use the disk controller to strip
> the other three and place one file per processor (create all the same
> size) within the file group. Normally I would break it out more but four
> disks isn't enough to do much with.
> "Jj" <willgart@.BBBhotmailAAA.com> wrote in message
> news:eT0XnuEfFHA.3692@.TK2MSFTNGP09.phx.gbl...
>
better disk config for staging & tempdb files...
Hi,
I want to have your feedback on how to configure my drives to insure a good
performance during loading process & transformation against a staging
database.
Image you have 4 drives available for the staging database & tempdb
database...
loosing these disk is not important, so no redundancy required.
I read from an external database into the staging, I do some updates and
copy into the staging himself, and finally I read the staging to load the
datawarehouse.
I execute more then 70 DTS packages, the sequence of these packages is
optimized. So I could write a fact table in the staging while I read another
to fill the datawarehouse at the same time.
I have 4 "big" fact tables (from 1 millions of rows to 20 millions)
There is "only" 4Gb to 5Gb used by the staging database during the loading
process.
how to configure these disks?
Does it better to setup all disks in raid 0? (the system do everything)
Does it better to keep the 4 disks separatly, and create multiple files &
file groups and dedicating 1 disk by fact table?
Creating 1 filegroup and 4 files in this file group (1 on each disk)? (SQL
Server manage the sharing usage of the disks)
Where to put log files?
thanks for your feedback.
Jerome.
Jerome,
What's the cost to the business if your DW is down for several days? Having
nonredundant disk for tempdb is generally unacceptable. Having nonredundant
disk for staging is a bit unusual. Push back to get more disk.
If I were placed in this situation and could not get more disk, I'd mirror
and strip with the controller and say that's all the performance she's got.
If management and the business owners are ready to signoff on the potential
of several days down time. The amount of down time depends on outside
hardware availability and support staff availability. Put your logs for
tempdb and staging on one disk. Use the disk controller to strip the other
three and place one file per processor (create all the same size) within the
file group. Normally I would break it out more but four disks isn't enough
to do much with.
"Jj" <willgart@.BBBhotmailAAA.com> wrote in message
news:eT0XnuEfFHA.3692@.TK2MSFTNGP09.phx.gbl...
> Hi,
> I want to have your feedback on how to configure my drives to insure a
> good performance during loading process & transformation against a staging
> database.
> Image you have 4 drives available for the staging database & tempdb
> database...
> loosing these disk is not important, so no redundancy required.
> I read from an external database into the staging, I do some updates and
> copy into the staging himself, and finally I read the staging to load the
> datawarehouse.
> I execute more then 70 DTS packages, the sequence of these packages is
> optimized. So I could write a fact table in the staging while I read
> another to fill the datawarehouse at the same time.
> I have 4 "big" fact tables (from 1 millions of rows to 20 millions)
> There is "only" 4Gb to 5Gb used by the staging database during the loading
> process.
> how to configure these disks?
> Does it better to setup all disks in raid 0? (the system do everything)
> Does it better to keep the 4 disks separatly, and create multiple files &
> file groups and dedicating 1 disk by fact table?
> Creating 1 filegroup and 4 files in this file group (1 on each disk)? (SQL
> Server manage the sharing usage of the disks)
> Where to put log files?
> thanks for your feedback.
> Jerome.
>
|||the cost is 0 if we can't load data.
a downtime of 4hour to change a disk if we don't have one in the office is
not an issue.
I don't understand why having the tempdb database on a non-fault taulerant
disk is not a good idea from your point of view!!!
tempdb is the only database recreated when er restart SQL server, so the
downtime in case of a crash is ... 5 minutes?
Thanks for the guide about 1 disk by processor.
I'll plan this config.
"Danny" <someone@.nowhere.com> wrote in message
news:1oRwe.14848$Xr6.6230@.trnddc07...
> Jerome,
> What's the cost to the business if your DW is down for several days?
> Having nonredundant disk for tempdb is generally unacceptable. Having
> nonredundant disk for staging is a bit unusual. Push back to get more
> disk.
> If I were placed in this situation and could not get more disk, I'd mirror
> and strip with the controller and say that's all the performance she's
> got.
> If management and the business owners are ready to signoff on the
> potential of several days down time. The amount of down time depends on
> outside hardware availability and support staff availability. Put your
> logs for tempdb and staging on one disk. Use the disk controller to strip
> the other three and place one file per processor (create all the same
> size) within the file group. Normally I would break it out more but four
> disks isn't enough to do much with.
> "Jj" <willgart@.BBBhotmailAAA.com> wrote in message
> news:eT0XnuEfFHA.3692@.TK2MSFTNGP09.phx.gbl...
>
I want to have your feedback on how to configure my drives to insure a good
performance during loading process & transformation against a staging
database.
Image you have 4 drives available for the staging database & tempdb
database...
loosing these disk is not important, so no redundancy required.
I read from an external database into the staging, I do some updates and
copy into the staging himself, and finally I read the staging to load the
datawarehouse.
I execute more then 70 DTS packages, the sequence of these packages is
optimized. So I could write a fact table in the staging while I read another
to fill the datawarehouse at the same time.
I have 4 "big" fact tables (from 1 millions of rows to 20 millions)
There is "only" 4Gb to 5Gb used by the staging database during the loading
process.
how to configure these disks?
Does it better to setup all disks in raid 0? (the system do everything)
Does it better to keep the 4 disks separatly, and create multiple files &
file groups and dedicating 1 disk by fact table?
Creating 1 filegroup and 4 files in this file group (1 on each disk)? (SQL
Server manage the sharing usage of the disks)
Where to put log files?
thanks for your feedback.
Jerome.
Jerome,
What's the cost to the business if your DW is down for several days? Having
nonredundant disk for tempdb is generally unacceptable. Having nonredundant
disk for staging is a bit unusual. Push back to get more disk.
If I were placed in this situation and could not get more disk, I'd mirror
and strip with the controller and say that's all the performance she's got.
If management and the business owners are ready to signoff on the potential
of several days down time. The amount of down time depends on outside
hardware availability and support staff availability. Put your logs for
tempdb and staging on one disk. Use the disk controller to strip the other
three and place one file per processor (create all the same size) within the
file group. Normally I would break it out more but four disks isn't enough
to do much with.
"Jj" <willgart@.BBBhotmailAAA.com> wrote in message
news:eT0XnuEfFHA.3692@.TK2MSFTNGP09.phx.gbl...
> Hi,
> I want to have your feedback on how to configure my drives to insure a
> good performance during loading process & transformation against a staging
> database.
> Image you have 4 drives available for the staging database & tempdb
> database...
> loosing these disk is not important, so no redundancy required.
> I read from an external database into the staging, I do some updates and
> copy into the staging himself, and finally I read the staging to load the
> datawarehouse.
> I execute more then 70 DTS packages, the sequence of these packages is
> optimized. So I could write a fact table in the staging while I read
> another to fill the datawarehouse at the same time.
> I have 4 "big" fact tables (from 1 millions of rows to 20 millions)
> There is "only" 4Gb to 5Gb used by the staging database during the loading
> process.
> how to configure these disks?
> Does it better to setup all disks in raid 0? (the system do everything)
> Does it better to keep the 4 disks separatly, and create multiple files &
> file groups and dedicating 1 disk by fact table?
> Creating 1 filegroup and 4 files in this file group (1 on each disk)? (SQL
> Server manage the sharing usage of the disks)
> Where to put log files?
> thanks for your feedback.
> Jerome.
>
|||the cost is 0 if we can't load data.
a downtime of 4hour to change a disk if we don't have one in the office is
not an issue.
I don't understand why having the tempdb database on a non-fault taulerant
disk is not a good idea from your point of view!!!
tempdb is the only database recreated when er restart SQL server, so the
downtime in case of a crash is ... 5 minutes?
Thanks for the guide about 1 disk by processor.
I'll plan this config.
"Danny" <someone@.nowhere.com> wrote in message
news:1oRwe.14848$Xr6.6230@.trnddc07...
> Jerome,
> What's the cost to the business if your DW is down for several days?
> Having nonredundant disk for tempdb is generally unacceptable. Having
> nonredundant disk for staging is a bit unusual. Push back to get more
> disk.
> If I were placed in this situation and could not get more disk, I'd mirror
> and strip with the controller and say that's all the performance she's
> got.
> If management and the business owners are ready to signoff on the
> potential of several days down time. The amount of down time depends on
> outside hardware availability and support staff availability. Put your
> logs for tempdb and staging on one disk. Use the disk controller to strip
> the other three and place one file per processor (create all the same
> size) within the file group. Normally I would break it out more but four
> disks isn't enough to do much with.
> "Jj" <willgart@.BBBhotmailAAA.com> wrote in message
> news:eT0XnuEfFHA.3692@.TK2MSFTNGP09.phx.gbl...
>
Subscribe to:
Posts (Atom)