Help with star Schema

  • Hello

    I am just starting to learn about database designing, so as a start I am trying to create a fulfilling Star Schema. I work for a utility company, so some of the values or going to be MWH, Gj, m3 etc.

    I've made a description trying to describe my thoughts behind the grains and dimension:

    Description of Star Schema for government reporting for Heating Department

    Fact Table in the center with specified Grains.

    Here follows my thoughts behind the Grains and their dimensions:

    -      Product

    The product is either heat or electricity

    -      Unit

    Units like tons, MWh, Gj etc.

    -      Amount

    Not really sure about this one, but there's a lot of different units, so they might be more of a conversion, where this is the number.

    -      Date

    Here the date down to day.

    -      ProductionUnit

    The machine that has used to produce the product.

    -      Time

    Hourly data

    • This topic was modified 1 week, 1 day ago by  cin.
    You must be logged in to view attached files.
  • Do you have an existing OLTP database to build from?

    What does each record in your fact table represent? What kind of reporting/summarizing do you want to do?

    The fun part is that if this isn't that big, you can build one in Excel and test it. Of course, you may need to do some really simple DAX, but that's easy.

  • Might be a silly question, so feel free to ignore this, but do you really need the DATE and TIME in 2 different columns?  I have seen systems do this and it can be a pain in the butt long term when a simple DATETIME (or DATETIME2) column can handle both DATE and TIME.

    I know there are use cases where having them separate is preferred, but just from working with GP where the Date is stored in one datetime column and the time is stored in a different datetime column, it can be a pain to work with.

    The above is all just my opinion on what you should do. 
    As with all advice you find on a random internet forum - you shouldn't blindly follow it.  Always test on a test server to see if there is negative side effects before making changes to live!

  • Hey

    Thanks for replying 🙂

    No, I don't have anything existing. I am starting to learn SQL with PostgreSQL and PgAdmin, which is the first kind of database in building.

    I tried describing it in the text:

    It a report that has to be send to my government, which declares what has been produced, the emissions it has caused and stuff like that. So it is not financial, but an operations report.

    Product - what is produced

    Unit - the unit it is measure in (MWh, Gj, M3 etc.)

    Amount - how much is produced. (I guess now that i think about it, this doesn't make sense, since it is determine in product?)

    Date - When its produced. (Gonna include the Time grain here)

    ProductionUnit - On which machine it has been produced

    Time - I'm gonna erase this, and include it in the Date.


    Would be awesome if i could test it. But wouldn't it require me having access to the databases?

    Can you maybe guide me to a place where I can see a bit more about how to test this stuff?

    Again, thanks for helping 🙂


  • Hey

    Thanks for replying 🙂

    Yea, i had the same though when i made it, so im gonna delete the Time grain and incorporate it into the date dimension 🙂

    Thanks for your help!

  • You need to have some Facts in your Fact table. DimAmount should be part of the Fact table called Amounts. So just merge that in there. Other than that I think you did a good job of identifying dimensions.   For star schemas modeling look to:

    Chris Adamson’s Blog

    Star Schema The Complete Reference: 9780071744324: Computer Science Books @

    $26.32 - 4.7/5 stars

    Analyzing Data with Power BI and Power Pivot for Excel - SQLBI

    Introduction to Data Modeling for Power BI Video Course - SQLBI

    Articles & Design Tips Archives - Kimball Group

    Kimball Toolkit Books on Data Warehousing and Business Intelligence

  • In Microsoft Power BI, best practice is to separate Date and Time to make model size smaller. Optimizing High Cardinality Columns in VertiPaq - SQLBI

Viewing 7 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply