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

learn more… | top users | synonyms

1
vote
2answers
72 views

Dimension for Product Properties based on Product Type - Sparse Dimension?

I am creating dimension of product properties for sales facts. Property of product depend of product type. For example: - Type = smartphone. Properties = model, OS, size - Type = book. Properties = ...
0
votes
2answers
40 views

How can I handle delayed records a month or older on a table clustered on time?

I have a two part question really for anyone working with data warehousing where they have large fact tables. Question 1 Lets say you have a table that has 500 million or more records in it that is ...
4
votes
1answer
338 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
1answer
41 views

Are there best practices or standardized syntax for writing transformation logic?

This may not be the appropriate forum, but its the closest I could find. I write a lot of source to target mappings for work. These documents just describe the operations/logic/transformations/etc. ...
1
vote
1answer
62 views

SQL Server : ETL Stage Index

We have a very large staging table (> 80 GB). From our source system we load invoice data in the staging table. From the staging we transform the data and load into DWH/Fact. Every day we delete the ...
1
vote
1answer
24 views

Table for actual values

I have a database where logging happens and is processed in a data-warehouse styled environment. This means there are staging cycles where the data comes in and then is processed. For a reporting ...
0
votes
1answer
32 views

ssis surrogate key in parent child tables

I have two tables Person & Ownership having one to many relationship (each person have many ownerships). Primary Key is person_id of type GUID. I am developing SSIS package to load data from both ...
0
votes
1answer
145 views

Time out while connecting to the database. Current time out value is 30 seconds

I am installing a repository creation utility on a windows 8 x64 bit system and getting the following error: Time out while connecting to the database. Current time out value is 30 seconds When ...
0
votes
1answer
44 views

Dimensional Model for inheritance in dimensions

How are dimensions typically modeled in a dimensional model if one dimension logically inherits from another dimension? For example, assume I have an Employee dimension and a Driver dimension, where ...
2
votes
0answers
234 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 ...
2
votes
0answers
332 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
193 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
0answers
805 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. ...
1
vote
0answers
25 views

Which Azure Storage / Database Technology to make use of for large scale logging

I am looking into making use of Azure hosting services in order to develop a logging service for our company. Azure has a number of storage options available (I have listed links below). However I am ...
1
vote
0answers
90 views

ETL flow in Fact tables

I've got a simple theoretical question... If we discuss the principles, elements, design and implementation of a typical ETL flow for a dimension table then, in my understanding, (in short) I'd say ...
1
vote
0answers
20 views

Process dimension task is querying the wrong column!

I'm in the process of building a new data warehouse and I am starting out by building a few dimensions. I have created a fairly typical "Date" dimension and that seems ok. Now I am trying to create ...
1
vote
0answers
67 views

Best practices to segment people and attributes for advertising database

we have a database where we store people some attributes per person in the following tables: people (id, name, email, phone) people_attributes (id, person_id, attribute_name, attribute_value) ...
1
vote
0answers
97 views

how many fact tables do I need given I want to build an OLAP for Quotation Line Item and Purchase Order Line Item report?

My client wants a report that looks something like this on a daily basis. +---------------+-------------------------+-----------------+---------------------+-------------+ | ProductNumber | ...
1
vote
0answers
351 views

500GB Data Warehouse - how to split it into FileGroups?

OK, so I have: 500 GB DW 5 GB in smaller DBs 220 GB TempDB 350 GB in Log files. My machine is Fujitsu Primergy 64 cores (with HT) and 192 GB RAM. I have several IO locations: 540 GB in-server ...
1
vote
0answers
134 views

Should I use conventional or computed columns for trivial metrics in a fact table?

A fact table can often have measures which are basically trivial. For example Orders.NumOrders will probably be "1" for every real record (synthetic records representing "no order" may populate this ...
0
votes
0answers
6 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 ...
0
votes
0answers
4 views

What is the Difference between Data Transformation vs Transportation?

Is there a difference in meaning between "data transformation" and "data transportation" in ETL process and data warehousing? or both concepts can be used interchangeably in this context? Thanks
0
votes
0answers
5 views

Is REDCap a Data Warehouse Environment?

I working with a web application used to capture research data called The Research Electronic Data Capture (REDCap) system. I am assign to a project to manage the clinical data that are captured in ...
0
votes
0answers
33 views

commonly used diagram for ETL

A the title indicates. Am i wondering what is the most commonly used diagram to explain an ETL process? In my case the process is rather linear, would a flowchart suffice? or is a Use-case also ...
0
votes
0answers
45 views

Which Azure service for use as data warehouse/analysis store?

We currently have a database running in SQL Server 2012 Standard. We need to export data periodically (a "data dump") of a particular set of data to "a location to be decided" where the client's ...
0
votes
0answers
22 views

Database choice for structured big data to be queried with SQL

I have a system that harvests and registers a total of 500K (master table) and 100 million (detail table) into a two table master/detail database, per day. The detail table has around 50 fields, two ...
0
votes
0answers
26 views

Fact Table with day grain that has many records that don't change

So I have a daily snapshot fact table that stores financial account balances. Normally, this would be pretty easy, just have a record for every day for every account. My problem is that table would ...
0
votes
0answers
9 views

Pentaho getfilename error

I am try to send 20 mail with attachments using pentahi DI, Jobs are like below Get File Names--->Add constants--->Mail But some mails are not send with below error, Thay say files are not ...
0
votes
0answers
38 views

SSAS bridge table with Slowly changing dimensions

I am designing a datamart using the basic principles of Kimball for use in SSAS. I have a many many to many attribute relationships (multi-valued dimension objects), however I have a few questions: ...
0
votes
0answers
39 views

What options exist for replicating SQL Server over the public internet

Specifically, I'd like to set up a reporting copy of a SQL Server 2012 transactional database. This primary runs in a managed services provider, and I want a copy in an AWS EC2 SQL Server instance. ...
0
votes
0answers
74 views

Proxy for MDW data collection on remote servers - SQL Server 2012

I am trying to setup data collection (for disk usage) from remote servers and upload it to my MDW database. I understand I will need a proxy set up for the agent to do this but which subsystem to I ...
0
votes
0answers
25 views

Data Warehouse for reporting - 6 Dimension tables,or 1 table and 6 views?

Problem: I have been asked to add a Dimension table to our data warehouse and create the ETL tasks to populate it. Lets call the table "DimInternalFault". The source table for DimInternalFault is ...
0
votes
0answers
12 views

Datavaulting polymorphic association (conditional) columns

I'm trying to implement data vault into our business but I'm stuck with the data vault model. We are having some source tables (3NF) which contain some polymorphic association (conditional) columns: ...
0
votes
0answers
137 views

Handle Duplicate entries in Fact table in Data Cube

We have been constructing data cube for our project in which we are facing a problem like 1 to many relationship in the dimension tables and making entries in fact table (duplication). ...
0
votes
0answers
53 views

Is there a way to check which views have data? Oracle 11g

I'm undergoing a cleanup process for a data mart design. However, I want to get rid of any views (in the design) which result in having 1 or less rows of data. Instead of manually going through ...
0
votes
0answers
187 views

Data warehousing: data transfer process too slow with larger batches

I have two primary instances of SQL Server, one for OLTP and one for reporting. The data warehouse for reporting is basically a Type 2 SCD: every table has DWDateStart and DWDateEnd columns and the ...
0
votes
0answers
64 views

Physical Table being used a Temp Table in a warehosue. Any advantage?

As I stated in previous posts I've inherited a warehouse based on SQL Server framework. In my continuous review of existing components and the practices adopted by the predecessors I found something ...
0
votes
0answers
41 views

Accounting - Dimention table in mysql

I am designing a accounting and mini erp system. I use a date dimension table to track all date related data. In oracle we can have many schema for a database. so we could create each schema for ...
0
votes
0answers
103 views

Is there better way to design SQL tables

I need to design a quick application for small warehouse. I just want to check if there is a better design approach. I am using polymorphic associations for comments tables. Business Rules If ...
0
votes
0answers
198 views

How much RAM do I need?

Let's assume a query like the following as typical: select A.`date` as `date`, count(*) as N from A as A join B as B on B.id = A.b_id join C as C on C.id = B.c_id join D as D on D.id = ...