A database system optimised for reporting, particularly in aggregate. Often, but not always implemented using a star schema.

learn more… | top users | synonyms

1
vote
1answer
41 views

Looking for ideas on maintaining data warehouse in close to real-time - ETL tools, etc

I have done a fair number of traditional data warehouse implementations where the loads were done in a batch-oriented manner, i.e. the data is refreshed nightly or, at most, every few hours. I now ...
0
votes
0answers
20 views

How can I find which records of `Data Warehouse` must be updated according to CDC captured changes? [on hold]

I use SQL Server 2012 for OLTP Database and created a Data Warehouse in SQL Server 2012. I design Facts and Dimensions inside Data Warehouse with several Views. Some of my Fact table are made by ...
0
votes
1answer
55 views

data warehouse design help

I'm struggling with the design phase for a data warehouse. It's not the traditional sales example you find on MSDN or all books etc. I've been tasked with creating an education data warehouse, that ...
2
votes
1answer
255 views

Review Technical ETL process for knowledge management

I'm looking for source code and all related component/material to review technical ETL process between source database and Data Warehouse in order to increase my knowledge in technical Business ...
1
vote
1answer
216 views

creating a report on the mysql jasperReport server

I have mysql local database, in it I have sample data mart. I have installed jasperReport server to my computer. Just using this tool, I want connect to the local mysql database and just create a pdf ...
7
votes
2answers
258 views

Data Warehouse design for reporting against data for many time zones

We are trying to optimize a data warehouse design that will support reporting against data for many time zones. For example, we might have a report for a month's worth of activity (millions of rows) ...
1
vote
0answers
44 views

What type of fact table / loading solution for a reservation system?

I am designing a Data Warehouse with SQL Server 2012 and SSIS. The source system handles hotel reservations. The reservations are split between two tables, header and header line item. The ...
0
votes
2answers
81 views

Daily snapshot fact table

I have a list of products per costumer. Costumers can delete or add products to their list. I want to know in each day what is the list of products (and count) by costumer. My first approach is to ...
1
vote
2answers
53 views

Using a data warehouse staging database as a source of operational data

Should you use the staging database within a data warehouse as a source of operational data? ie, is it good practice for other operational (non BI) systems to source their data from this database? ...
1
vote
1answer
125 views

Fastest mechanism to move data between tables in SQL server

We are building a data warehouse. For some of our target tables, the only way to manage the process correctly is to re-transform all the data every time something changes. Currently we do this using ...
8
votes
2answers
287 views

Handling time zones in data mart/warehouse

We are starting to design the building blocks of a data mart/warehouse and we need to be able to support all time zones (our clients are from all over the world). From reading discussions online (and ...
1
vote
1answer
74 views

How to dynamically generate rows based on value in a column

I'm attempting to write a query to denormalize some data for integrating to our data warehouse, specifically around generating some time slots. In my source system, I have table that has a start time ...
0
votes
1answer
38 views

Override Dimension Column Value

I have a dimension with a column called 'Type'. There is a list of default 'Types' in this dimension. However, this 'Type' column can be overriden. i.e. each user can replace the default value with ...
0
votes
1answer
47 views

Beginning Data Warehouse Design [closed]

I am beginning to develop a Data Warehouse. The data does not already exist in a relational database. Instead it will be sourced from various 3rd party vendors. The data requirements have been drawn ...
0
votes
1answer
105 views

Benefits of using Staging Database while designing Data Warehouse [closed]

I am in process of designing a Data Warehouse Architecture. While exploring various options to Extract data from Production and putting into Data Warehouse, I came across many articles which mainly ...
2
votes
4answers
182 views

SQL Server: Database integration and synchronization

I have n SQL Server databases with the same data schema. On the other hand i have a web application that needs to retrieve, for some entities, the union of the information stored into the n ...
3
votes
2answers
1k views

ALTER TABLE SWITCH statement failed

ALTER TABLE SWITCH statement failed because column does not have the same ANSI trimming semantics in tables... Does anyone knows how to fix that on already existing table full of data? Well, I've ...
4
votes
4answers
582 views

best ETL design to transfer transaction tables records into the data-warehouse

I have 2 type of tables to populate the data-warehouse with every day, lookup tables or configuration tables with few 100s records, and thats easy where i just truncate and refill the table. but for ...
0
votes
1answer
107 views

SQL Server 2012 for data warehouse [closed]

I have to develop a data warehouse using SQL Server 2012. I'm confused about the release I could use... Standard or Enterprise? The database server will have 16 or 32 GB of RAM and a Quad-Core CPU. ...
5
votes
1answer
122 views

sql server auto statistics in data warehousing scenario

I have a relatively large data warehouse in SQL Server (200 GB). I have been experiencing really show execution times of a certain query (12 hours for a simple delete query with an inner join). After ...
0
votes
1answer
42 views

How Many Rows om Int and Big Int [closed]

How many rows does datatype int and big int retrieve in their table when you work with fact table in SQL server?
0
votes
0answers
53 views

MySQL: Error 1118 using create table select and barracuda file format

We want to create the equivalent of some materialized views in a read-only slave for really performant reporting. The queries used to generate these tables span 19 tables with a fair number of ...
0
votes
2answers
129 views

avoiding unique key collisions across tables

This might be related to the idea of data warehousing, but i'm not sure, though it is extreme theory. We have a table where records get written to first, let's call it tbl_recording which looks like: ...
1
vote
1answer
305 views

Datawarehouse - Dimension, Fact, or Degenerative dimension

I am building a warehouse for a billing system that bills a number of services/utilities on the same invoice, as such I am having real trouble conforming to a recognised design method for the ...
3
votes
1answer
313 views

Schema for analytics table in Postgres

We use Postgres for analytics (star schema). Every few seconds we get reports on ~500 metrics types. The simplest schema would be: timestamp metric_type value 78930890 FOO ...
0
votes
1answer
102 views

Dimensional model or Relational model for data warehouse? [closed]

I have a pretty trivial questions in data warehouse, I know we use Dimensional data model to construct data marts and use Relational data model in staging area. What data model technique we use to ...
0
votes
0answers
74 views

How much RAM do I need?

Let's assume a query like the following as typical: select A.`date` as `date`, count(*) as N from A as A join B as B on B.id = A.b_id join C as C on C.id = B.c_id join D as D on D.id = ...
0
votes
1answer
150 views

Data Warehouse - Slowly Changing Dimensions with Many to Many Relationships

As an example, let's say I have a fact table with two dimensions and one measure FactMoney table ProjectKey int PersonKey int CashAmount money The two dimensions are defined like this: ...
0
votes
2answers
109 views

Query to return fields of distinct values per key

In the process of building a data warehouse we are creating some aggregation tables for a reporting engine. We would like to build a single aggregation table from the fact table that can exist as list ...
4
votes
1answer
189 views

How to handle “many columns” in OLAP RDBMS

I have a fact that has around 1K different numerical attributes (i.e. columns). I would like to store this in to a column-oriented DB and perform cube analysis on it. I tried to design a star ...
2
votes
1answer
175 views

What database and setup can handle several million inserts per minute? [closed]

We have come to a point where for a few of our customers' systems, at peak times, events are generated at that crazy rate. That is, millions of new events per minute (between 1M and 2M). The ...
2
votes
0answers
110 views

Updating a big replicated Dimension (SQL Server PDW)

We use a SQL Server PDW appliance for our data warehouse. One of the tables in our warehouse is a replicated table with about 20 million rows. As part of our ETL process we need to expire old records ...
7
votes
3answers
704 views

What advantages does in-memory OLAP have over traditional systems with significant memory?

Do in-memory OLAP engines have advantages over the traditional OLAP engines backed by enough RAM to contain the entire cube(s)? For example, if I use a MOLAP engine (SSAS) and GB / TB of RAM where ...
-5
votes
1answer
132 views

how to being building a data warehouse [closed]

We have a ERP system that uses a DB in postgres, each table has many rows and we want to build data warehouse (data warehousing). Also we want to add BI using ETL where would begin? would use schemes ...
4
votes
1answer
227 views

Where and how to store my weird datamart

I could really use some help here. Here's my situation. I'm building a datamart in SQL Server 2005, which will furnish a single report (for now). We have 26 clients (medical organizations) that ...
0
votes
0answers
50 views

Automatically identify the grain of a table?

Can you identify the grain of a table just by looking at the cardinality of the data. For example say you have a new file with no documentation - to get the grain manually you would try: select ...
2
votes
0answers
123 views

Geographic Hierarchies in a OLAP Cube / Data Warehouse

Are the following geographic hierarchies correct? Do any US/Canadian area codes cross state/province lines? Do any US/Canadian counties cross state/province lines? Are there countries with counties ...
2
votes
1answer
106 views

Advices for modeling facts, time hierarchies based on two conditions

Sorry if my question does not belong to that SE but I tried to find the best place and it ended up here. My problem is this : I've got a table modeled as this in our datawarehouse env. : MYTABLE ...
2
votes
0answers
104 views

How to gracefully handle a source database restore from backup in a data warehouse

We are facing a challenging situation with our data warehouse source databases. Frequently these sources databases will be restored from backups. There is a high turnover of data entry persons who ...
3
votes
1answer
198 views

Should I snowflake or duplicate it across my facts?

I'm building up a data warehouse to be used by SSAS to create cubes on, and I'm debating between two possible schemas. In my warehouse, I've got two different fact tables that tracking daily changes ...
1
vote
0answers
310 views

sql server 2008 execution plan different on two production servers [closed]

We have two servers running SQL server 2008 R2, with identical databases, where we implemented a datawarehousing solution. The cube processing operation was taking a VERY long time in one of the ...
0
votes
2answers
282 views

Can a surrogate key and a primary key be in the same table?

For example we have: Surrogate key: ABC123 Primary key: 1 Name: James Is this legit in a Data Warehouse table?
6
votes
2answers
782 views

Is SQL Server data compression categorically good for read-only databases?

Some literature on SQL Server data compression I read state that the write cost increases to about four times what would normally be required. It also seems to imply that this is the primary downside ...
-1
votes
1answer
79 views

data warehouse construction from mdf and ldf file via etl

I have sample data warehouse stored in the form of mdf and ldf format. I want to use any ETL tool to extract data and then load datawarehouse on the specified database with username, password and ...
0
votes
1answer
1k views

SSIS convert DT_WSTR to date

I'm new to SSIS trying to covert data types. I'm pulling data from Google Analytics and loading data into a SQL 2008 database. The date column from Google analytics appears as "DT_WSTR" and the values ...
0
votes
2answers
142 views

Teradata performance statistics

I need some statistics regarding the performance of Teradata, something like with a given hardware, given nodes and given AMPs - a table which has thousands of rows can be read in 't' secs. I need ...
6
votes
3answers
361 views

Header and line item data source mismatch

I'm working with a star schema for a data warehouse and I am running into a problem with header and line items from different data sources. CREATE TABLE DataSourceAHeader ( OrderId INT NOT NULL ...
5
votes
4answers
2k views

Why are so many MPP solutions based on PostgreSQL instead of MySQL?

Astor Data, Greenplum and GridSQL all allow Massive Parallel Processing of SQL queries. They are also all built around PostgreSQL technology. Is this just because of licensing issues or are there ...
8
votes
2answers
1k views

100 TeraBytes Capacity Database - Resources and Time Estimates

I am working on a 'back of the envelope' calculation for a 100TB reporting database setup. I am seeking out thoughts from the experts here. Proposed environment: Storage Capacity ~ 100TB Tables ~ ...
1
vote
1answer
254 views

Modeling staff dimension in data warehouse

I need to write a data warehouse diagram. I've got a problem with staff area. I have to store a information about workers details like name, age etc., and workers job time and agreement details like ...