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

learn more… | top users | synonyms

4
votes
1answer
391 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 ...
8
votes
1answer
181 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 ...
2
votes
1answer
953 views

Does surrogate key assignment for a fact table require that the source data has natural keys?

Let's say I have a simple OLTP database with orders, products, and customers: And from it, I am building a data mart with an orders fact table, product dimension, customer dimension, and date ...
0
votes
1answer
222 views

How to simplify schema for multi database / hundreds of millions of rows data set

We currently have a MySQL data set that was started about 10 years ago that aggregates a log of data, and accumulates millions of rows each month. The people that developed our system started ...
3
votes
3answers
1k 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
108 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
142 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 ...
1
vote
2answers
537 views

SQL Server 2012 Data Warehousing Overall Flow [closed]

I am studying Data Warehousing in SQL Server 2012 I am attempting to learn the various tools included by practicing on the different tools separately. I have practiced with: AdventureWorks ...
8
votes
3answers
4k 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. ...
5
votes
1answer
311 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 ...
14
votes
3answers
7k 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 ...
4
votes
1answer
477 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 ...
2
votes
3answers
1k 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, ...
6
votes
2answers
365 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 ...
3
votes
1answer
279 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 ...
-7
votes
3answers
234 views

Business Intelligence = Technical OR Business Project? [closed]

Do you consider a project of Business Intelligence to be a technical or business project?
3
votes
2answers
2k 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 ...
9
votes
2answers
2k 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 ~ ...
2
votes
1answer
286 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 ...
0
votes
1answer
209 views

Using N-Tier in Business Intelligence

I have read that you also can apply N-Tier for Businesss Intelligence based on these criteria: *Presentation *Functional logic *Data What is your experience when you are using N-Tier? // ...
3
votes
2answers
3k views

Reindexing a large database

I have a very large database (millions of rows) which has been indexed. I receive a diff of rows from another source that I need to update in my database. The problem I am facing is that is it ...
1
vote
0answers
34 views

Monoblock, Multiblock queries?

What exactly mean the terms monoblock / multiblock queries in data warehouses when talking about materialized views? Does a monoblock query just use one data source (a normal table) while a multiblock ...
5
votes
3answers
254 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 ...
2
votes
2answers
332 views

Data warehouse: using day number in Epoch in a date dimension

In Chapter 2 Retail Sales of Ralph Kimball's The Data Warehouse Toolkit, the author describes a date dimension with three fields which appear totally alien to me: Date Key (PK) Date Full Date ...
8
votes
2answers
6k 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 ...
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: ...
2
votes
1answer
189 views

Is dimensional modelling useful for modelling tables of calculations?

I have a set of tables that look a bit like this: Key 1 | Key 2 | Key 3 | Var 1 | Var 2 | Var 3 | ... | Var 200 ------------------------------------------------------------- X | Y | Z | ...
1
vote
1answer
2k views

How to move data from Primary Data File to other data file without destroying replication

I have one Database which is replicated using transactional replication. Now, I want do spare some data of that database to another partition, because my server running out of disk space. I was ...
1
vote
2answers
181 views

Designing an Accumulating Snapshot with Repeated Groups, need advise

Situation I need to create a pie chart that has to be able to be represented at the following levels: District, Region, and School. At the student level it has to show the trend of the student over ...
9
votes
1answer
6k 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 ...
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
3answers
3k 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, ...
8
votes
3answers
836 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 ...
2
votes
1answer
350 views

Oracle Change Data Capture: Unsubscribed column results in change set record

I am using Oracle 11g's Change Data Capture to track changes on a database table. I am using synchronous mode to minimize latency. Here's an example to illustrate my problem: Published_Table ...
3
votes
2answers
4k 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?
5
votes
1answer
407 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 ...
2
votes
2answers
180 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 ...
3
votes
2answers
183 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 ...
1
vote
2answers
312 views

Indexing of Data Warehouse tables?

Maybe I have misunderstood something but I can recall reading an article which said that there is no reason to create non-clustered indexes on very large data warehousing tables? That is because ...
5
votes
1answer
283 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 ...
0
votes
2answers
2k views

Possible career paths for Oracle Certified Associate (OCA) [closed]

In order to be confident in databases as a entry level job candidate, I completed the OCA certification. Now I want to know what career paths follow this. Can one go into data-mining, BI, or ...
7
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 ...
7
votes
3answers
1k 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 ...
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 ...
1
vote
1answer
886 views

Design of fact table(s) and dimensions tables for data warehouse

Hi i'm newbie in Datawarehousing, how would you model this in a Data Warehouse: i wish design the Data Warehouse which give the answers of statistics relating to a baseball league For players in ...
3
votes
2answers
537 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 ...
2
votes
1answer
1k views

Tips for Optimizing Oracle Warehouse Builder 11.1 with Oracle DB 10.2

Are there any tips for improving performance of Control Center and Design Center in Oracle Warehouse Builder? At the client side it is very slow to open and refresh Design center and control center ...
1
vote
0answers
622 views

An advice to design a fact table with a scd as a dimension

I have a problem of conception and I would like to get your ideas on how I'll be able to do what I am aiming. My goal is to create a datawarehouse, with three dimensions and one fact table so far. ...
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 ...
7
votes
3answers
548 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 ...