A database system optimised for reporting, particularly in aggregate. Often, but not always implemented using a star schema.
18
votes
5answers
3k 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 ...
13
votes
2answers
2k 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 ...
11
votes
2answers
1k 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
4answers
559 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. ...
8
votes
3answers
673 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 ...
8
votes
1answer
2k 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 ...
7
votes
2answers
734 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 ~ ...
7
votes
3answers
440 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
973 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.
...
7
votes
2answers
2k 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 ...
6
votes
2answers
250 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
4k 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: ...
6
votes
2answers
1k 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
3answers
513 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 ...
6
votes
1answer
103 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 ...
6
votes
3answers
165 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
1k 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
151 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
3answers
196 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
557 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
236 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
1answer
322 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
3answers
277 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
663 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
229 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 ...
4
votes
1answer
1k 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 ...
4
votes
1answer
269 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
134 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 ...
4
votes
1answer
168 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
3answers
187 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
149 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 ...
3
votes
2answers
2k 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
88 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
1answer
182 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 ...
3
votes
2answers
385 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
3answers
363 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
1answer
66 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
68 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
188 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 ...
3
votes
1answer
73 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 ...
3
votes
2answers
163 views
What are the best Database Analytics Companies for analyzing several 1000s or Mio. of products,sum them together and analyze them for statistics?
We will get several thousands products in our database. The problem is that one product can have several different names e.g. "iPad 16GByte UMTS schwarz" or "iPad 16GB 3G black" and so on. But they ...
3
votes
2answers
116 views
how often to run Tablediff
I wonder if it is safe to run the Tablediff tool on a database fairly often?
Let's say every 15 or 30 minutes. Also, the Data in the DB is around 100 GB.
3
votes
1answer
143 views
How to represent aggregates in a data warehouse
I am building the dimensional model for a data warehouse (as an exercise for a mini-course I am doing) and I want to build an aggregate to speed up queries.
Basically, I want the aggregate to group ...
3
votes
0answers
102 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
0answers
117 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 ...
3
votes
1answer
68 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 ...
2
votes
2answers
568 views
ETL: extraction strategy for 200 source databases
What is the best extraction strategy for approximately 200 SQL Server 2005 source databases (same schema) for a daily load into a staging area in preparation for data warehouse cleansing, ...
2
votes
2answers
130 views
How to compare the database of a backup from another environment
Has anyone had any luck with taking a backup (approx 30 tables) from an Oracle database's live environment, then loading a test environment with this backup and doing a comparison between the test ...
2
votes
2answers
116 views
Disaster recovery plan - Having multiple servers
I work in an organization where departments are in different buildings. I have a data centre in the main building. All the in-house applications connect to the SQL Server residing in the server in the ...
2
votes
1answer
196 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 ...
