The tag has no usage guidance.

learn more… | top users | synonyms

8
votes
2answers
2k views

Is my understanding of Fact table granularity correct?

Myself and another DBA at our company are tasked with reviewing a database design that a vendor has developed for us. The vendor has said they use Kimball as the basis for their design. (NOTE: I am ...
7
votes
3answers
868 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 ...
4
votes
2answers
780 views

Open source and commercial tools to build dynamic queries of fact tables in star schema [closed]

What user interfaces or tools (commercial and/or open source) can query a star schema model (Ralph Kimball dimensional model)? So for example, if we have X fact tables, and Y dimension tables, and Z ...
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
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
226 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
44 views

Duration measure in fact table

I have a fact table with the user reservations with StartDate and EndDate. I am thinking what is the proper way of serving the duration queries in the reports. Should I calculate DurationDays and ...
2
votes
0answers
47 views

Clustered index on a fact table

I'm trying to work out what field(s) to use for the clustered index of my fact tables (which all have a similar structure). I'll use FactSales as an example: CREATE TABLE dbo.FactSales ( ...
2
votes
0answers
814 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
3answers
1k views

Improve performance of a Fact Table

I have a Fact table CardTransactionFact Table Structure TABLE [dbo].[CardTransactionFact] [CardTransactionID] [int] IDENTITY(1,1) NOT NULL, [TransactionTerminalID] [int] NOT NULL, ...
1
vote
2answers
982 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 ...
1
vote
1answer
940 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 ...
1
vote
1answer
5k views

SSAS -> New cube contains two fact tables. How do I relate these so I can use measures from both at the same time?

I am working on a new cube. I know that you cannot directly create a relationship between two fact tables (and expect good results). I am just wondering how to go about doing this I have read ...
1
vote
3answers
145 views

Data Warehouse measures without a corresponding dimension

I have an operations database that contains this table: salesQuality salesPrice salesCategory According to my business requirements, the salesQality and salesPrice are measures. However, ...
1
vote
2answers
2k views

How to check if there is partitions in use in fact tables?

I'm going to take a first look of a large data warehouse with many fact tables. How can I easily check if there is partitions and partition functions in use in these fact tables?
1
vote
1answer
271 views

Having Non-Numeric values as measures in Fact table

According to my research regarding having non-numeric value as measures, I found that having non-numeric won't be an optimal one. So I planned to maintain numeric values. My scenario was "Employee's ...
1
vote
1answer
1k 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 ...
1
vote
1answer
20 views

Advice on Measures that have properties on their own

I'm looking for advice on how to model a database when the measures have an increasing number of properties - the values of which could be considered dimensions in their own right. In a simplified ...
1
vote
2answers
31 views

Non numeric attributes in fact table (to track data source)?

I am creating a data warehouse using star schemas, following the Kimball methodology. One of the rules is only put numeric values in fact tables. I am considering putting an "id" in a fact table, in ...
1
vote
0answers
98 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
469 views

What is the difference between bridge table and helper table in data warehouse's context?

From what I could understand :- Bridge tables are used when a dimension table can not be directly associated with a fact table. e.g. In a bank's data warehouse a fact table of balance of a customer ...
1
vote
0answers
116 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
140 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
1answer
60 views

FK Issue with Fact Table

I have a Datawarehouse star schema. One Dim table is 'DimTweet'. This contains tweet details. For a selected date I want to be able to display the number of tweets. However, I have a problem in my ...
0
votes
1answer
72 views

How to design this kind of relations in a database

I have a conceptual problem 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 database with information of persons who work at a place depending on ...
0
votes
1answer
20 views

Problem with Processing Dimensions with Multiple Facts

I have a cube built in SSAS that uses 3 facts and 8 dimensions. Some of the dimensions are shared across 3 facts. When I process one of the dimension, it tries to process the underlying measure ...
0
votes
0answers
26 views

Query slow on 5 Mil fact table

I have a fact table with currently 5 mil rec. The record count will go up as ETL runs on daily basis. The below reporting dashboard query is taking 1.50 sec to execute. There are many number of ...
0
votes
0answers
23 views

How many degenerated dimensions should be stored in Fact table

How many degenerate dimension should actually be stored in a fact table? I am building a star schema and have to store some columns (10 of them to be precious) which are on a per transaction level. ...
0
votes
1answer
358 views

What to do when facts change frequently in a star schema for data warehouse?

I have a Quotation Management system that I built as a CRUD web app. Over time, I realized I need to improve it so that management can have their reports. Hence I studied the Kimball method of data ...
0
votes
1answer
352 views

Type of Foreign Keys for a Dimensional Model Fact Table?

I've been reading about dimensional models, and in particular fact tables, but I haven't really seen one in action (i.e. the actual content), just diagrams of the table structures and column names ...