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

learn more… | top users | synonyms

19
votes
5answers
5k views

What are some ways to implement a many-to-many relationship in a data warehouse?

The dominant topologies of Data Warehouse modelling (Star, Snowflake) are designed with one-to-many relationships in mind. Query readability, performance, and structure degrades severely when faced ...
14
votes
3answers
6k views

What are the arguments in favor of using ELT process over ETL?

I realized that my company uses an ELT (extract-load-transform) process instead of using an ETL (extract-transform-load) process. What are the differences in the two approaches and in which situations ...
13
votes
2answers
2k views

Open Source Business Intelligence/DWH solutions

I wonder that this question hasn't already been asked. Google only has very few results for me that don't show a high quality tool What are some Open Source (also free is ok) solutions for Data ...
9
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 ~ ...
9
votes
4answers
779 views

Where should one put indexes in a time dimension table?

After reading the Questions and Answers from this website about indexes, a question came to my mind. What if, one is using a time dimension table with the lower level of granularity being the day. ...
9
votes
1answer
5k views

Limit the degree of parallelism (DOP) available to any query

On Oracle Exadata (11gR2), we have a relatively beefy database. cpu_count is 24 parallel_server_instances is 2 parallel_threads_per_cpu is 2 We noted, through observation in Oracle Enterprise ...
8
votes
2answers
6k views

Sql Server 2012 data warehousing and different versions

With Sql Server 2012 there are 3 flagship editions: Enterprise Edition, Business Intelligence, Standard. The full comparison between the three: ...
8
votes
3answers
3k views

When should indexes be dropped and recreated?

We are constructing a data warehouse that initially will be 1 TB and will grow around 20gigs every month. For certain tables we are doing daily ETL processes and others we are doing weekly/monthly. ...
8
votes
1answer
164 views

Dimension and Fact for same entities?

I'm rather new at DW design and am working on a DW to model some IT infrastructure. The major problem/question at this point is how to model drive information. We will be collecting aggregate data ...
8
votes
2answers
491 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 ...
8
votes
2answers
5k views

PostgreSQL for high volume transactions and for Data warehousing

Am quite new to PostgreSQL, I have never done a large deployment using it before. But, I have good experience in enterprise solutions and I want to try and apply some of what I learned using ...
7
votes
3answers
928 views

Suggestion for Large SQL Server Database Design

We are creating a database in MSSQL 2008 R2 Standard where we will be storing a large number of records. We estimate 200 million+ records in one table annually and we are primarily INSERTing with very ...
7
votes
2answers
557 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) ...
7
votes
3answers
538 views

Data Warehouse Creation

I'm fairly green when it comes to this field. I'm a data analyst at a small hospital 8 months now, out of a 2 year degree in a new career field for me. Situation We have a Cobol "database" (I ...
7
votes
3answers
768 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 ...
7
votes
1answer
1k 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 ...
6
votes
2answers
341 views

What is the name of this schema pattern

Its been a few years... but I've run into a problem-set that reminds me of a schema design pattern and I'm trying to recall the details. Essentially, the pattern consists of one database which is 3 ...
6
votes
2answers
2k views

Fact table foreign keys null?

I am new to data mart design and need to clear a few concepts. I have read up a bit on dimension modelling where I see that fact tables store foreign key references to dimension tables. Now suppose ...
6
votes
1answer
178 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 ...
6
votes
3answers
444 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 ...
5
votes
2answers
320 views

Alternative to EAV for dynamic fields in a star schema data warehouse

I need to support dynamic fields and values in a big datawarehouse for storing API requests log, my user case is that I need to store all API requests query string and able to perform query against ...
5
votes
1answer
298 views

Database structure, relational or data warehouse?

Good morning, Im having some issues with how my database should be laid out and would appreciate some guidance. I have several tables (rainfall data), each containing the following columns: Date ...
5
votes
1answer
2k views

ETL: extracting from 200 tables - SSIS data flow or custom T-SQL?

Based on my analysis, a complete dimensional model for our data warehouse will require extraction from over 200 source tables. Some of these tables will be extracted as part of an incremental load and ...
5
votes
2answers
747 views

Cardinality rule for bitmap indexes

The Oracle documentation includes the following advice: A bitmap index should be built on each of the foreign key columns of the fact table or tables In that reference, there is even a bitmap ...
5
votes
1answer
1k views

Do I need to enumerate the columns used in a materialized view when creating the MV log?

I'm currently creating materialized view logs on a whole bunch of tables in order to support materialized views with FAST REFRESH. The materialized views we're creating contain approx 30 columns from ...
5
votes
1answer
284 views

When should startup parameter -E be used?

We are starting to build a data warehouse system using SQL Server 2008 R2. One of the senior DBAs told us to consider using a startup parameter -E for the SQL Server service. I read from Books Online ...
5
votes
1answer
278 views

DBA's Scope in datawarehousing Projects

As a DBA trainee, what needs to be known in order to be part of datawarehousing projects? I mean what resources are suggested to have an overview knowledge of how PL/SQL and other concepts are used ...
5
votes
3answers
391 views

Which databases support parallel processing across multiple servers? [closed]

I need a database engine that can utilize multiple servers for processing a single SQL query in parallel. So far I know that this is possible with the some engines, though none of them are feasible ...
5
votes
1answer
395 views

Fact table design - Level or contribution measures

I am trying to come up with a dimensional model for the following: The source data comes from game machines as meter messages. Basically, the game machine accepts payments and sends a snapshot of the ...
5
votes
1answer
522 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 ...
5
votes
3answers
1k views

Partition pruning with multiple date columns

I have a large table in Oracle 11g database that holds historical data from several years, so I would like to partition it by year. The problem is that the table has multiple date columns and they are ...
4
votes
3answers
1k views

How can I make my SSIS process consume more resources and run faster?

I have a daily ETL process in SSIS that builds my warehouse so we can provide day-over-day reports. I have two servers - one for SSIS and the other for the SQL Server Database. The SSIS server ...
4
votes
1answer
468 views

Big Data vs Relational Database on making structure of Data-ware house for telecom data analysis [closed]

I'm currently doing my final year project on telecommunication mining. On starting project I got confuse on choosing database. As data on mobile communication is going on increasing so I think Big ...
4
votes
1answer
354 views

Should I Link a Fact to Hierarchical Dimension at ALL Levels or only the most granular?

Related question about the same project here. I'm looking at two approaches to model a hierarchical relationship between my fact and dimension tables in a data warehouse for storing IT infrastructure ...
4
votes
1answer
378 views

Methodology & Architecture to Create ETL Process

What book or webpage can I read the architecture and methodology for creating ETL process? In other words, I'm looking for "how to do it" to create a ETL process when you have many source system to ...
4
votes
3answers
240 views

How do I add a time dimension to this forecasting database without having to duplicate every data point @ each reforecast?

I have a database designed to facilitate revenue forecasting. Revenue forecasting consists of tracking Orders which are recognized into Revenue at multiple points in time (each, a revenueDate). There ...
4
votes
2answers
2k views

Datawarehouse Design: Combined Date Time dimension vs. Separate Day and Time dimensions and timezones

We are just starting design for a new data warehouse and we're trying to design how our date and time dimensions will work. We need to be able to support multiple timezones (probably at least GMT, ...
4
votes
4answers
835 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 ...
4
votes
2answers
440 views

Isolation Level for a Data Warehouse

We are building out a data warehouse (and also some data marts as well) in our organization. My DBA skills have mostly been on OLTP type applications, but I am moving into supporting OLAP for our ...
4
votes
1answer
223 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 ...
4
votes
1answer
182 views

Is there a standard way to replace codes with values from a lookup table for reporting or analytics?

Suppose I have a "main" table that was probably heavily normalized and consists largely of columns that merely contain codes that are lookups into other tables (they are probably foreign keys but feel ...
4
votes
1answer
251 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 ...
3
votes
2answers
3k views

What is SQL Server Parallel Data Warehouse?

What is SQL Server Parallel Data Warehouse solution? What is the benefit of PDW compared to normal DW architecture based on SQL Server and SSIS, SSAS and SSRS?
3
votes
1answer
94 views

What to do with duplicate lookup information

I have multiple databases that I want to store in one data warehouse database. I am wondering how I design the import process to handle multiple lookup tables. For example, say I have 5 databases all ...
3
votes
3answers
921 views

How do you roll up header-level values on a fact table with line-item granularity?

From what I've read, the recommended granularity for a fact table based on an order header / line item structure is at the line item level. In the process, any additive values that apply only to the ...
3
votes
2answers
493 views

Combining retail sales and payment OLAP cubes

I'm using SQL Server 2005 Standard Edition Analysis Services (SSAS). I'm trying to build cubes from a retail OLTP database that can answer these kinds of questions: List down all products that were ...
3
votes
1answer
95 views

Architectural Question about Data analytics

I understand that in many OLTP architectures there is some ELT stuff which creates a OLAP Data warehouse model and then you can run data analytics on this. However, this can take some time to ...
3
votes
1answer
120 views

Reporting Table Design

I'm writing a system that allows users to store values against each week in October and November and each day in December - starting from the first Monday. I'm holding the raw data entered by the ...
3
votes
1answer
260 views

Proper Design for Datamart Invoice

I'm trying to design a set of fact tables for a datamart that will work in an ssas cube for one of our clients. One of the challenges I'm facing is that there appears to be some disperencies between ...