Skip to main content

Questions tagged [facttable]

The tag has no usage guidance.

Filter by
Sorted by
Tagged with
0 votes
1 answer
22 views

ideas to reduce association tables while building a data warehouse

I'm trying to create a data warehouse for my BI project. Initially I have a raw database that contains 100 data tables, most of them are association tables. My goal is to create normalized dimensional ...
Jalel MED's user avatar
0 votes
0 answers
14 views

Synthetic or derived fact tables

I have a doubt on how to model a situation where the granularity of what we show in the reports does not derive directly from measurements (i.e. classic "facts"), but rather, is ...
user2412218's user avatar
0 votes
2 answers
309 views

Replace primary key with unique constraint and other clustered index to improve query performance without doubling table size

I've got a SQL Server 2019 with a large fact table (currently ~100 million rows but will most likely be >1 billion within a year and continue to increase for a long time). The primary objective is ...
Martin Riddar's user avatar
1 vote
1 answer
96 views

Database Design: Modeling Bridge Tables

Team: I have a question regarding a requirement: One security officer works can work at many offices. Depending on the office that the security officer is working on that day, the officer can have a ...
dd_novice's user avatar
0 votes
1 answer
1k views

Handling multiple dates on fact table with performance concerns

I am working on a simple data marts and has once again stumbled with the handling date and time. This is a design on a vehicle maintenance form records to collect the status record/remarks for ...
user3118602's user avatar
0 votes
1 answer
63 views

How do we assign FK to a newly loaded data in fact table?

While reading up on SQL, I got confused on how a newly loaded data into the fact table will get its FK. Say using the example below (image taken from Google): This is my thoughts on how the data is ...
user3118602's user avatar
1 vote
1 answer
1k 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 ...
Jossy's user avatar
  • 83
2 votes
1 answer
870 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 ...
Irfan Gowani's user avatar
1 vote
1 answer
472 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 : ...
Pantea's user avatar
  • 1,432
1 vote
1 answer
44 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 ...
sksamuel's user avatar
  • 113
0 votes
0 answers
50 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 ...
Kevin Südmersen's user avatar
1 vote
3 answers
670 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 ...
JustGiveMeData's user avatar
0 votes
1 answer
44 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 ...
jordaniac89's user avatar
0 votes
0 answers
58 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, ...
masongirl's user avatar
0 votes
0 answers
199 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-...
danieltisdale's user avatar
1 vote
0 answers
167 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 ...
GettingItDone's user avatar
1 vote
0 answers
30 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 ...
Chantal Fourie's user avatar
1 vote
0 answers
997 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) ...
Bee-Dub's user avatar
  • 41
3 votes
1 answer
1k 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 ...
Bee-Dub's user avatar
  • 41
1 vote
1 answer
866 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 ...
Sandhya Balakrishnan's user avatar
1 vote
1 answer
169 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 ...
scampbell's user avatar
  • 143
5 votes
1 answer
909 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 ...
Andrew Wolfe's user avatar
1 vote
3 answers
1k 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....
adolf garlic 's user avatar
0 votes
1 answer
894 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 ...
Alistair's user avatar
  • 141
0 votes
4 answers
671 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 ...
bjornasm's user avatar
  • 177
0 votes
1 answer
150 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 ...
K09's user avatar
  • 1,394
0 votes
1 answer
885 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 ...
Anil's 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 ...
n4cer500's user avatar
  • 141
1 vote
2 answers
622 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 ...
user2800708's 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 ...
LearnByReading's user avatar
2 votes
1 answer
354 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 ...
Vladimir Fedorov's user avatar
1 vote
1 answer
3k 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 ...
selva's user avatar
  • 175
1 vote
0 answers
392 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 ...
Milkncookiez's user avatar
6 votes
2 answers
13k 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 ...
aarushi's user avatar
  • 63
1 vote
0 answers
828 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 | ...
Kim Stacks's 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 ...
Kim Stacks's user avatar
2 votes
3 answers
995 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, ...
Marco Dinatsoli's 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 ...
Chris Aldrich's user avatar
1 vote
0 answers
383 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 ...
Jon of All Trades's user avatar
0 votes
1 answer
646 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 ...
Drewdavid's 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 ...
Dustin Kingen's 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 ...
Johnny Holmes's 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 ...
JustBeingHelpful's user avatar
2 votes
2 answers
4k 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 ...
Alan Hollis's user avatar
0 votes
1 answer
86 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 ...
edgarmtze's user avatar
  • 361
1 vote
2 answers
6k 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?
jrara's user avatar
  • 5,353
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 ...
8kb's user avatar
  • 2,629
2 votes
3 answers
3k 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, [...
AmmarR's user avatar
  • 2,816
8 votes
4 answers
6k 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 ...
8kb's user avatar
  • 2,629
2 votes
1 answer
10k 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 Smith's user avatar