data-warehouse

production schema

Hi, Can someone help me in understanding the difference between production schema and datawarehouse schema. Thanks in Advance Manoj Bhattu ...

Cross Database Unions

My warehouse datamart data is split across 3 databases on the same server. This is Proof-Of-Concept project with three extracts that were loaded into individual databases. While pulling into a cube, I am essentially doing this: SELECT * FROM DB1.dbo.Fact_Pres UNION SELECT * FROM DB2.dbo.Fact_Pres UNION SELECT * FROM DB3.dbo.Fact_Pres ...

Site-To-Site Data synchronization Over WCF

Hi, I'm developping a distributed solution with a WebSite and a Corporate Application Management. Here is the architecture : Web Site : Database (SQL Server) Web Site : ASP.NET MVC Data synchronization Services (WCF) - Corporate Management Application : Database (SQL Server) WinForm Application Data...

Tips for splitting database

I have read a couple of books on SQL Server 2005 but have not found a proper answer to what I am looking for. The problem is like this :- I have a database that is being used by 5-20 users at a time for booking customer orders. They receive many orders in a day on phone so placement of orders and lookup of products\old orders have to be...

Documentation Tempate for SSAS Cube

I need a template to document an SSAS cube. Cube info Single source of data One main Fact table Five direct dimensions Two many-to-many dimensions 48 Partitions, 48 Aggregations Can anyone point me to design templates for SSAS cubes? ...

How do i add a column to a fact table 'after' it is already deployed and populated?

Hi Guys I have a SQL Server 2005 data-mart star schema with the usual fact and dimensions tables. This is deployed and being populated via an SSIS based ETL package. All fine so far. I have just been approached by the customer with a new requirement. Never, I hear you say! This requirement will mean I need to add a new dimension table ...

Problem performance datawarehouse with lots of indexes

Our product takes tests of some 350 candidates at the same time. At the end of the test, results for each candidate are moved to a datawarehouse full of indexes on it. For each test there's some 400 records to be entered in datawarehouse. So 400 x 350 is a lot of records. If there are not much records in the datawarehouse, all goes well....

informatica mapping examples

i want to devlope generic mapping for handling data bases errors in Informatica. Can any one give me any examples of such mappings ? Also can u suggent me some resources for informatica samples mappings (livw webiste or links) ...

error handeling in informatca power center

i want to devlop a mapping for followinfg scenerio . I have a 1 source and 1 target and 1 error table.Target and Error tables have all fields that are present in source tables.But the data type o of all fieds for error table are varchar .Error table dont have integirty or foreign key and other constraints . Error table also have2 more f...

SQL Server 2005 SSIS Checksum Package

Folks, We're building an ETL process to load mid-size dimensional data warehouse using SQL Server 2005 SSIS on 64bit OS. We're planning to use SSIS's Checksum package to manage SCDs (Slowly Changing Dimensions). Even though we're doing a proof of concept using SSIS Checksum package, I'm not comfortable using it in real production scen...

Need help with SQL aggregation query

What is the most straightforward approach to producing a data set that can be used in a SQL Server Reporting Services report to display the following: SalesPerson # Sales # Gross Profit John Doe 100 $140,000 $25,000 Everyone Else (Avg.) 1200 $2,000,000 $250,000 Jane Smith ...

real-time data warehouse for web access logs

We're thinking about putting up a data warehouse system to load with web access logs that our web servers generate. The idea is to load the data in real-time. To the user we want to present a line graph of the data and enable the user to drill down using the dimensions. The question is how to balance and design the system so that ; (...

how to denormalize ranges and store them in a data warehouse?

My custom web servers report session information in the form of a wall clock timestamp (start of session) and the number of wall click seconds the session lasted. I want to store this information in a data warehouse (MySQL + start schema) in a way that will allow me to for example query the number of sessions for a particular entity a...

Data Warehouse Considerations: When and Why?

A little background here: I know what a data warehouse is, more or less. I've read several dozen guides on data warehousing, I've played with SSAS, I know what a star schema and a dimension table and a fact table is, I know what ETL is and how to do it. This is not a "how" question or a request for tutorials. My issue is that all of ...

Datawarehouse Tutorial

My boss has discovered a new magazine which mentioned data warehousing. Thus I am in search of a good tutorial or book on data warehousing. I will also accept recommendations on ways to stop my boss reading. ...

Strategies for populating a Reporting/Data Warehouse database

For our reporting application, we have a process that aggregates several databases into a single 'reporting' database on a nightly basis. The schema of the reporting database is quite different than that of the separate 'production' databases that we are aggregating so there is a good amount of business logic that goes into how the data ...

Partition Exchange as publishing technique in SQL Server?

I'm familiar with the concept of using partitions in Oracle as a technique to pubish incremental additions to tables (in a DW context). (like this example) For example. a daily snapshot for a data mart fact table is loaded behind the scenes in a partition within a table. for example with date as the partition key (1 partitioned table...

Many-To-Many dimensional model

Folks, I have a dimension table called DIM_FILE which holds information of the files we received from customers. Each file has detail records which constitutes my FACT table, CUST_DETAIL. In the main process, file is gone through several stages and each stage tags a status to it. Long in a short, I have many-to-many relationship. Any id...

Data in different resolutions

I have two tables, records are being continuously inserted to these tables from outside source. Lets say these tables are keeping statistics of user interactions. When a user is clicking a button the details of that click (the user, time of click etc.) is written to one of the tables. When a user mouseovers that button a record is added ...

Find deleted records from a type-2 table, efficiently

I've got a database table of type-2 data, and I want to find records that were deleted since I last synced with it. It's got date_from and date_to columns, and the raw data has an ID column object_id. date_to<>null means it doesn't exist now, so if there's no other record with the same object_id and date_to=null, then it's been deleted...