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
536 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
2answers
5k 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 ...
4
votes
2answers
751 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 ...
4
votes
1answer
175 views

What known strategies, if any, can I use to validate a Slowly Changing Dimension?

I have a customer dimension in our data warehouse (SSAS 2014 Multidimensional). It is set up as a Type II SCD, with just Start and End Date fields to track changes. (null end date = current). As our ...
4
votes
2answers
169 views

Is it good or bad practice to have a fact table relate to the same dimension table multiple times?

I'm new to DW/BI, and this seems like a pretty basic question, but I can't seem to find a good answer. I have a fact table that would have more than one date associated with it, so I was thinking ...
4
votes
4answers
3k 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
899 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
624 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
1answer
531 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
2answers
315 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 ...
4
votes
1answer
440 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
2answers
302 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.
4
votes
1answer
389 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 ...
4
votes
1answer
345 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 ...
3
votes
2answers
5k 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
97 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
4answers
2k 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
191 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
1answer
422 views

How do you calculate how much hardware resources you need for a database?

We are in the process of scaling our database servers. I was wondering how should we calculate how much hardware resources we need for our databases? Here is a little bit information about our ...
3
votes
1answer
1k 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 ...
3
votes
1answer
157 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
255 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
313 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
144 views

Stored procedure deployment - push or pull into database? [closed]

Using stored procedures as part of an ETL process to populate tables in another database, which method would be considered a best practice (if any) in terms of deployment, maintenance, visibility and ...
3
votes
1answer
967 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
2answers
3k 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 ...
2
votes
3answers
2k 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, de-...
2
votes
2answers
262 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
87 views

How many date dimensions for one fact

About designing star schema: I have three date columns in a fact table fact_table (insert_date, trade_date, close_date ...). And I don't know how many date dimensions should be created? Case 1: Dim ...
2
votes
1answer
323 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 ...
2
votes
2answers
354 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 ...
2
votes
3answers
29 views

Data Warehouse attribute that describes its timeframe?

I am writing a document about a Data Warehouse system, and would like to know if is there a single word (noun) that describes the DW attribute of how many time it contains? I mean, if a DW contains ...
2
votes
2answers
298 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
2answers
409 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 ...
2
votes
1answer
195 views

Update OLAP Cube data

We are using OLAP in our project and we are facing a scenario where we need to update the cube with real time data on daily basis. I need some suggestions about an optimal way for updating the data ...
2
votes
2answers
137 views

Do valid to/from columns on a fact table make sense?

I recently came across "valid from" and "valid to" columns on a fact table. Obviously this is common for dimensions but I've not seen them on fact tables before and can't find any info suggesting they'...
2
votes
1answer
54 views

Modeling Conversion Events in a Data Warehouse

How should I model conversion events? For instance, if a sales opportunity goes from "stage: prospecting" to "stage: validating", should that be treated as a changing dimension, or a conversion event ...
2
votes
1answer
259 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
5k 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 ...
2
votes
1answer
703 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 records/...
2
votes
2answers
345 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 ...
2
votes
2answers
81 views

Best way to capture historical changes between levels in a dimensional hierarchy

What is the cleanest way to capture changes between levels in a dimensional hierarchy? I have the dimensional hierarchy [Area] > [Region] > [Location], where Area is the parent to Region and Region ...
2
votes
1answer
55 views

How to start indexing a new data warehouse

I am building a data warehouse (SQL Server 2012, Kimball Dimensional modeling, 750GB) that will supply data to reports (SSRS), cubes (SSAS) and various datafeeds. I plan to use an index tuning tool (...
2
votes
2answers
587 views

Have a separate database for data warehousing?

I have built a typical web application using PHP and MySQL for enterprise usage. The MySQL database is 3NF as much as possible. However, as time goes by, the requests for the data appear to be more ...
2
votes
2answers
778 views

TSQL vs SSIS Lookup

I was trying to find some well performing Tsql equivalents for the SSIS Lookup component. Especially for Fact table loading. After few different query patterns the most obv performed the best: ...
2
votes
1answer
155 views

Hierarchy for dimensional model

I am trying to model a DW where I have many levels of geography (Neighborhood, District, City, State). I have several demographic data that should be included in the model. These data include count ...
2
votes
2answers
2k 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 ...
2
votes
1answer
194 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
1answer
85 views

Mixed granularity weeks/months time series data in a data warehouse?

I'm not sure that "anti-aliasing of time series data" is the correct terminology to use, so let me explain: I have some sources of data that are aligned quarterly, mostly to do with quarterly running ...
2
votes
2answers
247 views

When to choose an attribute vs. a new dimension?

I am fully aware of what is a fact, attribute and dimension. However, I'm quite confused to which traits I should choose for dimensions vs attributes of that dimension. For instance, I'm building a ...