Database Administrators Stack Exchange
Database Administrators Stack Exchange

Questions tagged [star-schema]

59 questions
20
votes
2 answers

Difference between star schema and data cube?

I am involved in a new project where I have to create a data cube from the existing relational database system. I understood that the existing system is not properly designed, and I am not sure where to start. My question are: What is difference…
Rathish Kumar B
  • 2,320
  • 5
  • 24
  • 36
13
votes
2 answers

Alternative to EAV for dynamic fields in a star schema data warehouse

I need to support dynamic fields and values in a big datawarehouse for storing API requests log, my user case is that I need to store all API requests query string and able to perform query against them in the future (so it is not just storage, so I…
Howard
  • 249
  • 1
  • 5
  • 10
8
votes
2 answers

Best approach for populating date dimension table

I am looking to populate a date dimension table in a SQL Server 2008 database. The fields in the table are as follows: [DateId] INT IDENTITY(1,1) PRIMARY KEY [DateTime] DATETIME [Date] …
JohnLinux
  • 255
  • 1
  • 4
  • 9
8
votes
2 answers

Benefit of having time dimension in a star schema?

What would be the benefits of having a time dimension in a star schema over having the time attributes in the fact table itself? For example: I have a transaction data with user information for each transaction, country where the transaction took…
rolladice
  • 83
  • 1
  • 4
7
votes
1 answer

When are dimension tables still needed using clustered columnstore indexing?

I am using MS SQL Server 2016 Clustered Columnstore Indexing (let's call it CCI) in my reporting database. In initial designs I was thinking star schema but then I started playing with CCI. Now I have discarded many dimension tables in favor of…
Cyndi Baker
  • 695
  • 7
  • 19
5
votes
1 answer

Schema for analytics table in Postgres

We use Postgres for analytics (star schema). Every few seconds we get reports on ~500 metrics types. The simplest schema would be: timestamp metric_type value 78930890 FOO 80.9 78930890 ZOO 20 Our DBA…
user2976991
  • 53
  • 1
  • 5
5
votes
2 answers

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 in dollar values. Each entity in these fact tables…
Evan M.
  • 231
  • 1
  • 8
5
votes
2 answers

Cardinality rule for bitmap indexes

The Oracle documentation includes the following advice: A bitmap index should be built on each of the foreign key columns of the fact table or tables In that reference, there is even a bitmap index on the date column. Whatever happened to…
user1831003
  • 75
  • 1
  • 4
5
votes
2 answers

What are the difference between a "dimension" table in a star-schema and a "lookup" table in a relational database?

I'm trying to design a star-schema fact table along with some dimension tables that will surround it. If I reuse the natural key called customer_key in both the fact_table and dim_customer then I don't see a difference between calling something dim…
Krolique
  • 153
  • 1
  • 5
5
votes
1 answer

Building slowly changing dimension on a Fact/Dimension Star Schema

I have heard textbook definitions of how to design a star schema regarding what goes in the fact table and what goes in the dimension tables, such as: The fact table should contain core information about an object and dimensions should contain…
Michael Plautz
  • 165
  • 2
  • 7
4
votes
1 answer

What known strategies, if any, can I use to validate a Slowly Changing Dimension?

I have a customer dimension in our data warehouse (SSAS 2014 Multidimensional). It is set up as a Type II SCD, with just Start and End Date fields to track changes. (null end date = current). As our daily load runs and I see the changes flowing in,…
IronicMuffin
  • 663
  • 5
  • 15
4
votes
1 answer

How to handle "many columns" in OLAP RDBMS

I have a fact that has around 1K different numerical attributes (i.e. columns). I would like to store this in to a column-oriented DB and perform cube analysis on it. I tried to design a star schema, but I'm not sure how to handle this many…
Enno Shioji
  • 185
  • 3
4
votes
2 answers

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

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 cross reference tables, we'd like to query a…
JustBeingHelpful
  • 2,116
  • 18
  • 45
  • 61
4
votes
1 answer

Design star schema for many-many relationship

What are the steps/rules to build a DW star schema design from a production database; specifically, how do you handle many-to-many relationships. I understand how to take basic data including a many-to-many relationship, and get to a normalized…
hqt
  • 199
  • 2
  • 8
4
votes
2 answers

Star Schema from Relational Database

I'm a newbie to data warehousing and I've been reading articles and watching videos on the principles but I'm a bit confused as to how I would take the design below and convert it into a star schema. In all the examples I've seen the fact table…
saj
  • 93
  • 1
  • 7
1
2 3 4