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

learn more… | top users | synonyms

0
votes
1answer
2k 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 ...
4
votes
1answer
224 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 ...
0
votes
2answers
172 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
445 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 ...
1
vote
0answers
449 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 ...
8
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 ...
3
votes
1answer
215 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
1answer
322 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 ...
1
vote
1answer
220 views

Open source tools to manage Data Quality [closed]

Are there open source (or commercial) tools that can report data quality issues in a data warehouse using the Kimball star schema model? It doesn't have to be the silver bullet. I'm just looking for ...
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 ...
1
vote
1answer
175 views

Conceptual Design of a data warehouse (declaring the grain)

I am trying to design a data warehouse using this example scrubbed data. I need to declare the "grain". I know its the lowest level of detail in the fact table, but I am having a hard time determining ...
2
votes
2answers
157 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 ...
4
votes
1answer
188 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 ...
1
vote
2answers
423 views

Data warehouse support tickets

I'm starting to design a data warehouse for a company. The first questions we're trying to resolve are regarding their support ticketing system. My initial schema is as follows Now one of the ...
4
votes
1answer
472 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 ...
0
votes
1answer
245 views

What are good resources on data modelling for business intelligence? [closed]

We are a SaaS startup offering purchase & procurement solutions. The data model for operational transactions has been created (relational DB). We also want to offer analytics on those transactions ...
1
vote
2answers
199 views

XML Large DataBase is the best? [closed]

Is the best way to implement a large database is XML? Or is there a better way? 1 - reduce the time complexity 2 - Reduce the amount of traffic between the tables And other factors ... Which ...
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
761 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 ...
1
vote
1answer
290 views

Force chain to start immediately after completion instead of on a timer

Is it possible to force an Oracle chain to restart immediately after it finishes executing, instead of on a timer? I have only found ways to do a temporal restart, which doesn't really work for me as ...
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 ...
3
votes
2answers
200 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.
2
votes
1answer
126 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 ...
5
votes
1answer
288 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
2answers
451 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 ...
2
votes
1answer
1k views

In star schemas, what should I do when a dimension has a date attribute?

I'm trying to learn about dimensional models and star schemas. Say I have a Sales fact table recording the total sales at retail stores, with, say, four dimensions, Date, Customers, Stores, and ...
0
votes
1answer
40 views

Oracle scheduler chain priority in data warehouse applications

The scenario is as follows: We have a data warehouse type application that relies on the Oracle job scheduler and a Chain. This chain is responsible for generating materialized views (caches) that ...
0
votes
2answers
288 views

SQL Server 2005 replication for a data warehouse

We are trying to implement some kind of Data Warehouse at my current employer. Most of our data is in a SQL Server 2005, but we might also integrate some Access databases and some databases in a SQL ...
3
votes
1answer
321 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 ...
4
votes
1answer
357 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
167 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
774 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
205 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
924 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
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 ...
1
vote
2answers
477 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
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. ...
5
votes
1answer
299 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
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 ...
4
votes
1answer
387 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
342 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
261 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
230 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
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 ...
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 ~ ...
2
votes
1answer
274 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
194 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? // ...
2
votes
2answers
2k 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 ...