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
2answers
264 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
342 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
301 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
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?
3
votes
1answer
95 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
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
2answers
189 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
646 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
126 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
205 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
300 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
38 views

Scripting Microsoft Data Warehouse Wizard

I am trying to automate the MDW setup process in my environment. Currently I have everything complete except for enabling the data collectors (usually uses a wizard as seen ). Has anyone seen or ...
3
votes
1answer
29 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
695 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
1answer
236 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 ...
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 ...
2
votes
1answer
120 views

Server roles in SQL Server 2016

There are 3 new server roles in SQL Server 2016: mediumrc largerc xlargerc Any idea what these are for? Google doesn't give any information about these.
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, ...
2
votes
2answers
195 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
330 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
1answer
5k views

Best approach for populating date dimension table

I am looking to populate a date dimension table in a SQL Server 2008 database. The fields in the table are as follows: [DateId] INT IDENTITY(1,1) PRIMARY KEY [DateTime] ...
2
votes
2answers
226 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
309 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
381 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
2answers
71 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 ...
2
votes
1answer
202 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
548 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 ...
2
votes
2answers
258 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
1answer
43 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
84 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
1k 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
181 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
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 ...
2
votes
2answers
31 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 ...
2
votes
1answer
66 views

Update Cube data

We are using OLAP in our Project. 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 in ...
2
votes
1answer
78 views

Is data warehouse monitoring same as database monitoring? [closed]

I have a requirement, where data warehouse population is to be monitored. I am in confusion, and needed a clear picture. Is data warehouse different than database? eg. For monitoring database we ...
2
votes
4answers
456 views

SQL Server: Database integration and synchronization

I have n SQL Server databases with the same data schema. On the other hand i have a web application that needs to retrieve, for some entities, the union of the information stored into the n ...
2
votes
1answer
2k 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 ...
2
votes
1answer
196 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 | ...
2
votes
1answer
397 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 ...
2
votes
1answer
2k 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 ...
2
votes
0answers
141 views

SQL Server, Mount Points and File Groups design [closed]

I am looking for some advice on a data warehouse we are building. Specifically around SQL Server, mount points and file groups. We have two design options for how mount points and files groups should ...
2
votes
1answer
555 views

Is it advisable to add indexes to a data warehouse's staging area?

We have a data warehouse built in MSSQL 2012, Staging area, EDW, cubes and reports. In the staging area, the tables are a 1:1 copy of those supporting the systems used to fill the DWH with. The ...
2
votes
0answers
247 views

Running materialized view refresh in parallel

I have a large materialized view that is refreshed periodically (complete refresh) and I would like to do it in parallel. I can already run the query part in parallel but the problem is (much slower) ...
2
votes
0answers
182 views

How to gracefully handle a source database restore from backup in a data warehouse

We are facing a challenging situation with our data warehouse source databases. Frequently these sources databases will be restored from backups. There is a high turnover of data entry persons who ...
2
votes
1answer
185 views

Migrate Management Data Warehouse to a New Instance

Is there a defined procedure to follow for moving a SQL Server 2012 management data warehouse to a new server\instance? We intend to setup an MDW on a test sql server instance first, but will then ...
1
vote
3answers
970 views

Purpose of Data Warehousing?

I have been reading up on Data Warehousing and its purpose. The main purpose seems to be to track historical data and whatever changes may have occurred over time. The DW book I am reading gives a ...
1
vote
2answers
168 views

How to architect an analytical BI platform? [closed]

I am working on a new BI project for a medium sized corporation. Right now there is no Analytical infrastructure in place and the reporting is done manually in Excel. There are a few different data ...
1
vote
1answer
615 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
2answers
744 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 ...