Questions tagged [facttable]

The tag has no usage guidance.

Filter by
Sorted by
Tagged with
1 vote
1 answer
37 views

How to deal with fact table data that needs to be version controlled?

I have the following simplified sport_match 'fact' table: match_id tournament_id player_id_p1 player_id_p2 p1_final_score p2_final_score 1 1 1 2 1 0 2 1 1 2 3 1 3 2 3 2 2 3 4 2 3 2 4 0 The table ...
user avatar
  • 131
0 votes
0 answers
19 views

Need help creating a transaction-grained fact table

For a personal project I need to design for the first time in my life a (star schema to keep it simple) relational database, the context is the sport of boxing. Specifically, I need help designing a ...
user avatar
2 votes
0 answers
154 views

Dimension modelling for HR with Employee Dimension and multiple departments in a Data warehouse

What is the best way to configure a dimension model (preferably star schema) when we have the following requirements? There is an Employees table (25 attributes) where we are required to make some of ...
user avatar
1 vote
1 answer
129 views

How to handle NULL Dates in an "Accumulating fact table"

One type of fact table in Dimensional modeling is Accumulating Snapshot fact Table. if you think you need to review the meaning and definition of this type , you can take a look at this article : ...
user avatar
  • 1,302
1 vote
1 answer
26 views

Type 2 Dimension for a fact measure

I have a need to store products along with their prices and quantities (and many other things) and track the history. Now, given both price and quantity are stored as integers, it is my understanding ...
user avatar
  • 113
0 votes
0 answers
25 views

Distinguishing between fact and dimension tables in data warehouse

Is it true that facts in a fact table of a data warehouse always have a relation to time? I.e. is it true that a fact must always be assignable to a specific point in time? If this is correct, can we ...
user avatar
1 vote
3 answers
175 views

The right way to model multiple FACTs schema

Background I'm in a process of designing a database (using a STAR schema). There are three tables to model: products, tests, states. The database will be used to store results of tests conducted on ...
user avatar
0 votes
1 answer
34 views

Deciding what values should be used in a data warehouse dimension?

I'm beginning to look at data warehousing and I'm coming across examples where, for instance, we have check payment transactions. I understand that this should be the "fact" and we would have ...
user avatar
0 votes
0 answers
35 views

Trying to do a fact and dim table in Sql

so far this is what i have code wise, but im getting an error 1239 . Is it not referencing something correctly . CREATE TABLE CustomerDim ( C_CustomerID_Cu INT(10) NOT NULL, ...
user avatar
0 votes
0 answers
51 views

How to handle table that has an id which references multiple dimension tables?

I'm having a hard time figuring out how to create a lookup table or a table that references multiple dimension tables. I've got a "tracking" table that tracks the number of impressions for an e-...
user avatar
1 vote
0 answers
45 views

How does one determine the number of FACT tables that are required in a data-warehouse? [closed]

When analysing data sources that need to be included in the data-warehouse (Kimball), how does a developer determine whether a schema should be a star type or constellation type? Business processes ...
user avatar
  • 109
1 vote
0 answers
11 views

SSAS Design 2 Fact tables (1 Weekly, 1 Monthly) [closed]

I am currently busy building a sales cube for a customer. However they would also like to see their current stock count in the same cube. The Sales Fact table is on a daily grain, where as the Stock ...
user avatar
0 votes
0 answers
550 views

Dimensional Model for CRM Sales Opportunity and Sales Leads: Fact or Dimension?

I'm working for a business lender, and trying to come up with a basic dimensional model around the subject area of loan approvals. We use a CRM, (Salesforce) and one of the core objects is a (Sales) ...
user avatar
  • 31
3 votes
1 answer
757 views

Dimensional Modeling of a Loan Approval Process and Fact Grain(s)

Context: I'm modeling for a lending company and I'm modeling our loan approval process. I have some basic dimensions relative to our business figured out: Time(day) Account Opportunity ...
user avatar
  • 31
1 vote
0 answers
409 views

My Fact table has data weekly but it is not visible in SSAS cube

I have my fact table that has data in weekly basis, so I am mapping with Week dimension to pull records in Piviot table. My ssas cube doesnt show all data that is in fact. Should I add any specific ...
user avatar
1 vote
1 answer
101 views

SSAS Cube Only Displays Total Values

When browsing my cube it shows the correct invoice values by date when I select only a date type (the date table was generated by SSAS with hierarchies), but if I add the customer name it shows the ...
user avatar
  • 143
5 votes
1 answer
472 views

If a DW fact table is not uniquely identified by all dimensions, what problems?

This is a bit of a thought problem I've been wrestling with. I have a visceral antipathy to the notion of duplicate combinations of dimension values in a fact table. I've read a lot about the ...
user avatar
1 vote
3 answers
781 views

Should a Fact table contain a column with a single unique value?

In order to simplify reporting, I am wondering if for each of the Fact tables we have, whether I can include a column that states the name of the Fact table, albeit in a more user friendly format e.g....
user avatar
0 votes
1 answer
722 views

Periodic snapshot fact table with monthly grain - Question on dimensions

I am fairly new to data-warehousing but have read my fair share of books and online tutorials to gain an introductory understanding of the basic components that build up a data warehouse. My end goal ...
user avatar
  • 141
0 votes
4 answers
544 views

Database design to record occurences in tv show

I want to make a database where I record the guests of each episodes of a tv show. I want to be able to do the following stastics: What guest had the most appearences? Which two (or three) guests ...
user avatar
  • 177
0 votes
1 answer
134 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 ...
user avatar
  • 1,344
2 votes
0 answers
251 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 ( ...
user avatar
  • 501
0 votes
1 answer
859 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 ...
user avatar
  • 1
1 vote
1 answer
22 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 ...
user avatar
  • 141
1 vote
2 answers
483 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 ...
user avatar
4 votes
2 answers
2k 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 ...
user avatar
2 votes
1 answer
230 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 ...
user avatar
1 vote
1 answer
2k 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 ...
user avatar
  • 75
1 vote
0 answers
388 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 ...
user avatar
6 votes
2 answers
12k 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 ...
user avatar
  • 63
1 vote
0 answers
771 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 | ...
user avatar
0 votes
1 answer
4k 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 ...
user avatar
2 votes
3 answers
819 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, ...
user avatar
8 votes
2 answers
5k 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 ...
user avatar
1 vote
0 answers
361 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 ...
user avatar
0 votes
1 answer
610 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 ...
user avatar
  • 145
7 votes
3 answers
2k 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 ...
user avatar
2 votes
1 answer
2k 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 ...
user avatar
4 votes
2 answers
2k 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 ...
user avatar
  • 2,005
2 votes
2 answers
3k 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 ...
user avatar
0 votes
1 answer
83 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 ...
user avatar
  • 331
1 vote
2 answers
5k 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?
user avatar
  • 5,193
3 votes
1 answer
2k 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 ...
user avatar
  • 2,549
2 votes
3 answers
2k 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, [...
user avatar
  • 2,756
7 votes
4 answers
5k 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 ...
user avatar
  • 2,549
2 votes
1 answer
9k 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 ...
user avatar
2 votes
1 answer
2k 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 ...
user avatar
3 votes
0 answers
1k 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. ...
user avatar
  • 2,248