Questions tagged [facttable]
The facttable tag has no usage guidance.
50
questions
0
votes
1
answer
403
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 ...
- 137
0
votes
1
answer
41
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 ...
- 137
1
vote
1
answer
265
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 ...
- 131
0
votes
0
answers
21
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 ...
2
votes
0
answers
429
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 ...
- 21
1
vote
1
answer
324
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 :
...
- 1,390
1
vote
1
answer
31
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 ...
- 113
0
votes
0
answers
33
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 ...
- 101
1
vote
3
answers
289
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 ...
- 13
0
votes
1
answer
36
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 ...
- 103
0
votes
0
answers
44
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,
...
0
votes
0
answers
124
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-...
1
vote
0
answers
109
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 ...
- 109
1
vote
0
answers
17
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 ...
1
vote
0
answers
750
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) ...
- 41
3
votes
1
answer
930
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
...
- 41
1
vote
1
answer
608
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 ...
1
vote
1
answer
132
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 ...
- 143
5
votes
1
answer
648
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 ...
- 584
1
vote
3
answers
956
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....
- 257
0
votes
1
answer
795
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 ...
- 141
0
votes
4
answers
616
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 ...
- 177
0
votes
1
answer
143
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 ...
- 1,384
2
votes
0
answers
265
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
(
...
- 501
0
votes
1
answer
867
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 ...
- 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 ...
- 141
1
vote
2
answers
538
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 ...
- 243
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 ...
- 711
2
votes
1
answer
266
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 ...
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 ...
- 75
1
vote
0
answers
391
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 ...
- 399
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 ...
- 63
1
vote
0
answers
800
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 | ...
- 145
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 ...
- 145
2
votes
3
answers
892
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, ...
- 151
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 ...
- 4,896
1
vote
0
answers
363
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 ...
- 5,877
0
votes
1
answer
617
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
...
- 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
...
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 ...
- 315
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 ...
- 2,062
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 ...
- 123
0
votes
1
answer
85
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 ...
- 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?
- 5,263
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 ...
- 2,609
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,
[...
- 2,776
8
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 ...
- 2,609
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 ...
- 307
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 ...
- 121
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.
...
- 2,248