Loading Data Warehouse (Perf. Tuning)
Feb 25, 2008
Hi all,
I'm loading my data warehouse using several SCDs. Some of these SCDs need to occur in sequence, while others can be run at the same time. I'm wondering what the best option for me is in terms of performance. Here is what I was considering:
1) Create a single package. Create two sequence containers --- one that will contain SCD loads that occur in sequence; the other sequence container contains SCD loads that occur in parallel.
OR
2) Create a set of packages for each SCD load. Then create a "Master" package that will use "Execute Package Task" components to call these packages.
The othe reason I want to bring up these difference ways to design an DW Load is because the second option is a "cleaner" approach, or a more organizational approach, to the load. The first option can get quite messy and large if you have several SCDs and several sequence containers. However, I'm looking for the fastest performance. Any thoughts?
View 7 Replies
ADVERTISEMENT
Feb 19, 2004
Howdy
I have a server that has the following average readings :
No. CPUs = 1
% CPU = 2
SystemCPU Queue Length = 2 to 4
SQL Server:Buffer ManagerBuffer Cache = 99.85%
RAM in the box = 1 GB
MemoryPages/sec = 1 to 5
SQL memory in use ( using Task Manager ) = 250 MB
Max worker threads = 255
Average number of connection = 60
So...........all indicators are that the CPU is idling, there is way enough RAM but we still have a ( in theory ) a congested CPU as the queue length is over 2 consistantly. Thing is, I need to work out if the CPU isnt working hard as the queue is long, or whether we can put extra databases/load on the box.
As the max worker threads are greater than number of connections ( 60 vs 255 ) we could reduce these as the number of users doesnt seem to alter much. BUT.......would this make much difference as if the 255-60=195 worker threads arent doing anything much, they shouldnt put any load on the server, right?
Any thoughts much appreciated.
Cheers,
SG.
View 13 Replies
View Related
Feb 28, 2008
The challenge: I have to extract and convert data between 2 SQL server systems - only 4 tables on the source systems, 8 tables on the target system. Source tables have between 5,000 rows and 16,000,000 rows. For most of the tables (for example Customer, which goes into 4 target tables), there will be 1 row in target tables for each row in the mapped source system table - so my 13.5M customer rows will end up as around 40M rows across the 4 target tables. So far, so good. But - this is a 24x7 online retail web-site, and to get the data across as a clean process, we require the smallest possible duration.
I have progressed on the customer migration, and am testing on a test environment (2xdual core HT processors, 4 GB ram) which was 2.15 million rows. Live environment is likely to be a 4xdual core with 8-16 GB ram.
I am trying to optimize the extract data flow, and have read the SSISperfTuning doc. I am now trying to put that into practice.
I have a row size of approx 340 bytes, so based on that, and my test environment of 2.15 million rows, I work out at around 700 MB ram required to buffer the data. That is a factor of 7 times greater than the max buffer space for a data flow of 100 MB, which it seems, means I should divide the base MaxBufferRows (10000) by 7 to go down to 1400 rows?
I see a LOT of the following messages in my progress, when running with default settings:
[DTS.Pipeline] Information: The buffer manager detected that the system was low on virtual memory, but was unable to swap out any buffers. 30 buffers were considered and 30 were locked. Either not enough memory is available to the pipeline because not enough is installed, other processes are using it, or too many buffers are locked.
The design of the data flow at the moment is:
..........................................|--target table 1
SOURCE SP ---- MULTICAST---|--target table 2
..........................................|--target table 3
..........................................|--target table 4
any thoughts on Buffer tweaking, corrections to my assumption and other hints/techniques?
*##* *##* *##* *##*
Chaos, Disorder and Panic ... my work is done here!
View 7 Replies
View Related
Aug 12, 2015
I have a requirement to implement CDC for 50+ tables to implement incremental data changes warehouse/reporting rather than exporting the whole table data. The largest table is having more than half a billion records.
The warehouse use a daily copy of OLTP db (daily DB refresh). How can I accomplish this. Is there a downside in implementing CDC just for the sake of taking incremental changes on the tables?
Is there any performance impact if we enable CDC on OLTP db?
Can we make use of the CDC tables on the environment we do daily db refresh so that the queries don't hit OLTP database?
What is the best way to implement CDC to take incremental changes for reporting.
View 0 Replies
View Related
May 25, 2006
hi
I am new at MSSQL 2000 DBA thing. and trying to learn more about analysis service/data warehouse/data mining. so is any expert out there can Recommend some good books or web link article to read? Thanks
View 1 Replies
View Related
Oct 6, 2006
Hi Dear All!
I am Crystal Reports Developer and I am new in SSIS environment. I have started to read Professional SQL Server 2005 IS book. I am really confused by many tasks to choose.
I need to develop reports from data warehouse. But before I have to send the data from operational database (SQL Server 2000) to warehouse (SQL Server 2005) monthly - I have a script for retrieving the data. For my package, I chose Data Flow Task, Execute SQL Task, and OLE DB Destination, and it does not work.
Please help me if I can look similar packages performing?
Thank you!!
View 5 Replies
View Related
Apr 8, 1999
hi, Can anyone list the type of error that will make @@error =1
I created a procedure to update a table based on a cutomer id, Id 7 doest not exist in table A, and I am suppose to have Not valid id, but in this case nothing happen I always get table a updated
thanks
Ali
begin tran
update table a
set title =' manager '
where id =7
if (@error <> 0)
begin
rollback tran
print 'not valid id '
return
end
ELSE
begin
commit tran
print 'table A updated'
end
View 3 Replies
View Related
Oct 19, 2015
I need a recommendation on a data modeling tool that can be used with a data warehouse. My warehouse is running SQL 2012.
Here is my challenge: Most of the tables in the warehouse do not have primary keys and none of the tables have foreign keys on them. However, there are indexes and unique keys/indexes on the tables. I am looking for a tool that I can create virtual relationships on how the data is related, so it is visually easier for the ETL developers to write the code.
I have looked at both ER/Studio 11 and ERwin 9.6. Neither of them do it exactly the way I want it too. However, ER/Studio is pretty close.
View 0 Replies
View Related
Sep 24, 2015
I am working on to create a data warehouse. I have made a database which will be the data warehouse and will consist of dimension and fact tables. I know that other than dimension and fact table a data warehouse should also consist of a meta data, now my question is what should be the structure of metadata and all the information it should have?
View 2 Replies
View Related
Dec 22, 2004
I am going to use Microsoft SQL Server to develop my data warehouse, but one thing makes me confused. Since Analysis Service can create a Star schema database, do I have to pre-set up a Star schema database for ETLed data? Basically, I am wondering what's the relationship between an ETLed database and the one created through the Analysis Services.
Can any one give me an explanation from the implementation perspective?
Many thanks!
View 4 Replies
View Related
Feb 27, 2008
Hi all,
I am trying to restore my data warehouse from a January 2008 backup under a new name to recover a table that I accidentally deleted. It is taking a long time for the restore to get done. Here is the command I am running as sa in QA
---
RESTORE DATABASE Warehouse_new FROM DISK = 'H:MSSQLDataMSSQLBACKUPDBBackupsWarehouseWa rehouse_db_200801050600.BAK'
WITH
MOVE 'Warehouse_Data' TO 'G:MSSQLDataMSSQLDataWarehouse_New_Data.MDF',
MOVE 'Warehouse_Log' TO 'H:MSSQLDataMSSQLLogsWarehouse_New_Log.ldf'
----
There Warehouse_New_Data.MDF is 375 GB and the log is 12 GB.
There is still 169 GB of free space on the drive I am restoring to after the presence of Warehouse_Data.MDF and Warehouse_New_Data.MDF (each 375 GB).
Its been 4.5 hrs and the restore is still running. Backups take about 3.5 hrs to complete. Can I do any checks on the restore to see what point it is at? I stopped the restore using EM earlier after it took 8 hours and still no progress.
Please advise.
Thanks.
View 1 Replies
View Related
Jul 23, 2005
Has anyone seen a 25 TB data warehouse on SQL Server? I do not know ofsuch a thing, but maybe it exists out there.
View 6 Replies
View Related
Mar 15, 2006
Hello..I was wondering if anyone out there could tell me how they deal withNULL values in a data warehouse? I am looking to implement a warehousein SQL 2005 and have some fields which will have NULL values and Iwould like some further ideas on how to deal with them. At my last jobin dealing with Oracle we were just going to leave the fields NULL, butin SQL how would you best recommend cleaning the data? I greatlyappreicate your help and look forward to your reponses.Thank you
View 2 Replies
View Related
May 21, 2007
I€™m making warehouse for our HMIS (healthcare management information system)by using SSIS. I€™m facing some problems now, could you please help me to solve my problem.
Brief idea about my Warehouse:
Source: oracle 9i
Destination: Sql server 2005
ETL tool: SSIS
Problems:
How to refresh or load the current data to data warehouse.(now i'm using truncate sql task for deleting old/entire data for each packages, i really dont want to use in the production) . For example: The patient admissions data is adding everyday so i want to load the current data into my warehouse.
Could you pls suggest me good solution for this?
Refresh Cycle timings: is there any task available in SSIS?
current status:
First Time load completed, i set one Execute Sql statement ctrl flow task for Truncate the existing loaded data in the sql server 2005. and then again i process one data flow task for loading the data from oracle to sql server.
View 8 Replies
View Related
Jul 6, 2007
Hi, all,
Thanks for your kind attention.
Just have some enquries about some issues of data warehouse design and would like to hear from any of you for any prestigious forum on data warehouse?
Thanks a lot in advance and I am looking forward to hearing from you shortly.
With best regards,
Yours sincerely,
View 3 Replies
View Related
Apr 10, 2008
Is SSIS a tool for extracting realtime data from staging to data warehouse? Realtime in my case can be loading every 15 minutes but no more than 30 minutes. I've a data warehouse which data refresh once a day and it worked fine. The data that I extract into the warehouse is from a Staging database which is realtime replication of multiple production databases. Once a day, I've to have replication pauses on staging for a couple hours to refresh the data warehouse. That's the only way so SSIS can pull the data correctly; if I've replication on while SSIS pull data, it will always copy less rows than it supposed to.
I cannot afford to have replication pauses every 15 minutes just so I can refresh data warehouse. Does anyone every have this problem? or any best practice how to do this?
Thanks,
-Ash
View 2 Replies
View Related
Apr 25, 2008
Hi,
Can any one give some good articles and webcast links for Developing SSRS reports with Data WareHouse.
Regards
Sithender
View 1 Replies
View Related
Jun 26, 2003
Does anyone have a sample RFP for a Data Warehousing project?
My manager hired an outside consultant to draw up a proposal for our company. But it is getting stuck in details and we are way behind schedule.
It will help me greatly if there is an outline of a DW RFP.
View 4 Replies
View Related
Jun 9, 2004
Hello, everyone:
Does any body have the experience to execute data warehouse backup/recover? What I want to know is how to backup/recover database in data warehouse and cubes.
Thanks.
ZYT
View 5 Replies
View Related
Feb 28, 2008
At my office, we've been slowly working on putting together a data warehouse.
We're a financial services company and one of the services that we offer is debt collection. As far as reports go, our clients are interested in knowing how much money we collect over time. In particular, they want to know how many payments we've gotten 5, 10, and 15 months (and so on) after we receive a case. (Obviously, the 5-month payments are also included in the 10 and 15-month calculations).
When I wrote this report using our transactional database, I was completely new to SQL and the ever-resourceful Patron Saint took pity on me, so you can see a good description of the details at http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=78510
Now that I'm no longer a total newbie at SQL, and having been through a relatively extensive seminar on data warehousing, I've been entrusted with researching certain aspects of data warehouse development (rest easy, though, folks - the real DWH work is not being done by the very inexperienced me, but by an actual professional :) ).
My question:
how would you model this kind of "relative time" in a data warehouse? How would you display the 5-month, 10-month, and 15-month payments in a DWH? I can't really imagine that the kinds of joins necessary to do this in a transactional database would be desirable in a data warehouse.
We have the following:
1.) FACT_Payment: A fact table showing each each payment to the most detailed granularity. One attribute of this table is the payment date. Another attribute is a foreign key to the case dimension described below.
2.) DIM_Case: A dimension table showing information on each case, including the case start date. DIM_Case
3.) DIM_Date: A date dimension table.
(For added clarification: The FACT_Payment payment date has to be 5, 10, 15 months etc... after the DIM_Case start date.)
Any ideas, comments, experience with something like this?
Thank you.
View 7 Replies
View Related
Mar 12, 2008
Hi all,
I'm working on my first data warehouse and I'm not sure how I should name the columns in the database.
The first phase of the data warehouse is to store a bunch of data from one third party source. The source contains over 100 pieces of data and the business user doesn't even know what some of the fields are but he wants to store everything. The third party refers to the each field with a somewhat cryptic short name and a longer description. The short name isn't always cryptic.
My question is am I better off naming my columns the same as the source system's short name so that I can easily debug problems later? Should I instead try to shorten their definition into something meaningful? On a side note, I'm 100% positive that we'll never populate the tables in questions with data from an additional source.
Thanks!
View 3 Replies
View Related
Jun 19, 2007
I need someone to give me a small briefieng on how a enterprise data warehouse (EDW)differs from a regular database?
Currently we have an appplication that accesses a database with about 18 tables. We also have a Data Warehouse. For some reason I was thinking that it would be possibly to migrate thedatabase into the data warehouse. The reason is that in looking at the schematic design forthe data warehouse there are some data tables that could also be used by our application that uses the DB.
I guess I am confused because I am not sure if a data warehouse is used in the same wayas a database?
View 2 Replies
View Related
Aug 31, 2006
i want to create a data mart from an existing OLTP database. for example northwind or i will create an OLTP database. i dont know how i can create data mart from OLTP database. i want to learn that step by step. help me??? please!!
View 5 Replies
View Related
Jun 1, 2004
I'm reviewing a data warehouse design schema for a client that is following Kimball's data warehousing principles. One of the first things I noticed was a table of dates with expanded columns giving such information as the year, month, month name, fiscal year, quarter, etc for each date, They also have a surrogate key (int) for the date value. The fact tables store the surrogate key rather than the date value itself.
They were very surprised when I questioned the purpose of this table, assuring me that Kimball was very strong on the concept of having a date dimension for each table.
I don't see the purpose of a table containing nothing by derived date formats. I think they will get a bigger performance hit from having to link through the surrogate key than they would suffer from having to convert datevalues stored in the fact tables.
Has anybody else ever seen this before? Does Kimball really advise this?
View 14 Replies
View Related
Jul 20, 2005
Hi,I would like to know if anyone out there really uses SQLServer 2000 (which edition?) to hold the data for a datawarehouse? How much data does it handle efficiently?TIAFrank
View 1 Replies
View Related
Jul 20, 2005
Hello all,I just started a new job this week and they complain about the length oftime it takes to load data into their data warehouse,which they do once a month.From what I can gather, they rebuild the indexes before the insert with an80% Fillfactor, then insert the data (with theindexes enabled), then rebuild the indexes with a 100% Fillfactor.Most of my RDBMS experience is with a different product. We would havedisabled the indexes and Foreign Keys, loaded the data, thenre-enabled them, moving any records that violated the constraints into anappropriate audit table to be checked after.Can someone share with me what the accepted "best practices" are for loadingdata efficiently into a data warehouse?Any thoughts would be deeply appreciated.Steve
View 2 Replies
View Related
Jan 6, 2006
I have a question about staging design using SSIS. Has anyone come up with an ETL design that would read table names from a generic table and dynamically create the ETL to stage the table.
1. Have a generic table which would have table name and description and whatever else that was required.
2. Have a master ETL that would enumerate through the table and stage all the table names found in the generic table.
This way I wouldn't have to create an ETL which would hardcode the names of 300-500 tables and have the appropriate 300-500 data sources and targets listed.
Not sure if I am making sense but I hope someone understands the attempt.
thanks
View 10 Replies
View Related
Jan 19, 2007
Hi all,
Our star schema design has one fact table and 3 dimensions.
The FK's in the fact do not necessarily make up the primary key. So I have an identifier in the fact table as PK. Here is my index assignment:
Fact Table - Clustered Index on PK
Non Clustered Index 1 on FK1
Non Clustered Index 2 on FK2
Non Clustered Index 3 on FK3
Each Dimension Table - Clustered Index on PK
Non Clustered Index on Attribute. This is the attribute that will be used in reports / cubes.
Is the above design good to start with?
Thanks,
V
View 4 Replies
View Related
May 4, 2007
We have a relational database (rd) and a data warehouse (dw). This dw has a table (tw) with all key fields (dimension keys) and metric related (measures) fields. This table is populated with monthly data each month. The tw is joined to various look up views present in the dw to obtain name fields from rd. The DBA wants me to remove the look up views. I now have following 2 options that I can think of –
1)Further de-normalize the tw and store the name fields as well. However, there are two issues with this option –
a.The size of tw will grow tremendously.
b.We are storing monthly data and the values in the name fields may change after some time. Then we will have to put in additional views/objects to obtain the latest name.
2)Using ETL, obtain the copy of rd tables overnight in dw. We will then join tw with these tables and there will no longer be cross database joins. However, this will be a burden on maintenance and support.
As of now these are the possible options I can come up with. Which one would you suggest and why? If you have another option, please let me know.
Thank you all in advance,
sajmera
View 1 Replies
View Related
Apr 1, 2004
I would like to buy the book "The Data Warehouse Toolkit by Ralph Kimball" but unfortunately only the 1st edition is currently available over here and they say that it will take at least 6 weeks if I want to order the 2nd edition.
Can someone please guide if there are MAJOR differences between the 1st and the 2nd edition of this book? If not, then what are the differences if any.
I'll appreciate your reply.
View 2 Replies
View Related
Jul 10, 2015
We have a master database (SQL 2014 Std) from which data are imported from XML files (send by en ERP system) using SSIS. Â There is about 12 other servers (SQL 2014 Express) located in remote warehouses. People will uses PocketPC to scan barcode of products in the warehouses and all operations must be forwarded to the master DB to be exported in a XML file for the ERP system.Now, each warehouses are independant. How can I setup the replication so only data belonging to a specific warehouse is replicated to its corresponding DB? I thought about creating views, one for each warehouses, and setup a replication for each warehouse, so there would be 12 merge replications configured. Is it fine?
View 2 Replies
View Related
Aug 21, 2014
OK, so I have:
- 500 GB DW
- 5 GB in smaller DBs
- 220 GB TempDB
- 350 GB in Log files.
My machine is Fujitsu Primergy 64 cores (with HT) and 192 GB RAM.
I have several IO locations:
- 540 GB in-server HDD 15k RAID10
- 1 TB HDD 15k RAID10 on SAN (separete controller)
- 2 TB HDD 15k RAID10 on SAN (same controlller as below)
- 800GB SSD RAID10 on SAN (same controller as above)
Data warehouse has 2 fact tables that are absolutely crucial and quite large.
Now i want to organize DB into several Filegroups and put them on different drives. Filegroups I'm thinking of:
- FILEGROUP1: for 1st crucial Fact Table
- FILEGROUP2: for 2nd crucial Fact Table
- FILEGROUP3: for tempDB
- FILEGROUP4: for dimensions data
- FILEGROUP5: for the rest of facts data
- FILEGROUP6: for dimensions indexes
- FILEGROUP7: for the rest of facts indexes
- FILEGROUP8: for 1 log file of one smaller DB (its in full-recovery and its quite large)
- FILEGROUP9: for the rest of log files
- FILEGROUP10: others
How should I organize them across available drives? I was thinking about sth like:
800 GB SSD: FILEGROUPS 1-3
2 TB RAID10: FILEGROUPS 5+7+8
1 TB RAID10: FILEGROUPS 4+6+10
540 GB in-server: FILEGROUP 9
I know that having multiple filegroups on the same drive is pointless regarding performance, but in future i could actually add some more drives, so i want to separate them now.
Also - how much files per filegroups should i create? Considering 1 or 2. Except TempDB where I am going for 4.
View 2 Replies
View Related
Aug 12, 2010
I have tried to set up the Management Data Warehouse on one of our production servers, but it is not working (not collecting any data) so I want to completely remove it and try the installation again. There does not seem to be any remove/uninstall option.
How to completely remove the Management Data Warehouse?
Version:
Microsoft SQL Server 2008 (SP1) - 10.0.2531.0 (X64)
Mar 29 2009 10:11:52
Copyright (c) 1988-2008 Microsoft Corporation
Enterprise Edition (64-bit) on Windows NT 6.1 <X64> (Build 7600: )
View 7 Replies
View Related
