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 RepliesI 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.
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
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!!
I have  a column in which Email data is available likeÂ
clicuanan@aspenms.com(M)
jteply@mac.com(M)
How to extract in the below format
clicuanan@aspenms.com
jteply@mac.com
tjones@jpmc.com
Hi All,
Bit of a funny one. I have two tables, lets call them PetOwner and Pets
PetOwner
OwnerID OwnerName
1 Tim
2 Paul
3 Andy
Pets
OwnerID PetName
1 Tricia
1 Louise
2 Bill
2 Ben
2 David
2 Topsy
3 Flopsy
3 Mopsy
And I need to write a query that returns a resultset like this
OwnerID OwnerName PetName1 PetName2 PetName3 PetName4 PetName5
1 Tim Tricia Louise NULL NULL NULL
2 Paul Bill Ben David Topsy Flopsy
3 Andy Flopsy Mopsy NULL NULL NULL
Can anyone help?
N.B. A pet owner can only have a maximum of 5 pets, but even if no-one in the database has 5 pets, the PetName5 column must still appear (but have a value of NULL).
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.
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?
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 RelatedI need to copy data from warehouse tables to master tables of different SQL instances. Refresh need to done once in an hour. What is the best way to do this? SQL agent jobs or SSIS packages?
View 3 Replies View RelatedI have a problem with a PIPE "|" delimited flat file. I have a column "Description" in which we get a string in which we have PIPE "|" as data. How we can skip this and load it as a data into the column Description.
View 7 Replies View Relatedhi, 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
I am preparing for the exam anybody know of any braindump sites that have data
warehousing braindumps?
Hello,
I am new to SQL Server. I want to know about DATA WAREHOUSING AND OLAP. Please help me.
Can anyone suggest a good resource that outlines data warehouse solutions using SQL 7.0 or higher?
View 1 Replies View RelatedHi,
I need to implement/set up the Data warehouse/Data mart in one of the department in my company by using SQL server 2005. Do any body knows the steps what I need to follow?
It will be more appreciate that, if any body gives some of the links which will help me to do the implementation/development of the same.
I do have the basic idea however I may face some of the difficulties when I start such as, does the SQL server reporting service allow the end user to customize the report based on their needs etc.?, so any of them having experience in this field please reply me.
Thanks
Ajith Nair
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!
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.
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 RelatedHello..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 RelatedI€™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.
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,
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
Hi,
Can any one give some good articles and webcast links for Developing SSRS reports with Data WareHouse.
Regards
Sithender
Hi:
I am planning to give the microsoft 70-019 data warehousing exam. I am not able to find any good resources to prepare for the exam. Please help!
Thanks in advance
Hi
We are starting with designing a datawarehouse for my company. I have done some reading on the concepts and steps involved, but what I am seriously lacking is some examples. I'd like to read through some real examples of data warehouses that worked including the full design diagrams.
Can anyone direct me to some good sites for this?
Thanks,
TeaaZA
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.
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
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.
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!
Hello, everone:
Anybody can offer technical information about DTS appied in data warehousing? Website, tutorial, forum and books will be great. Thanks.
ZYT
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?
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