November 18, 2021 at 3:09 pm
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
November 18, 2021 at 6:43 pm
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.
November 18, 2021 at 7:14 pm
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!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
November 19, 2021 at 7:06 am
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 🙂
November 19, 2021 at 7:07 am
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!
November 22, 2021 at 2:58 pm
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 @ Amazon.com
$26.32 - 4.7/5 stars
Analyzing Data with Power BI and Power Pivot for Excel - SQLBI
https://www.sqlbi.com/books/analyzing-data-with-microsoft-power-bi-and-power-pivot-for-excel/
Introduction to Data Modeling for Power BI Video Course - SQLBI
https://www.sqlbi.com/p/introduction-to-data-modeling-for-power-bi-video-course/
Articles & Design Tips Archives - Kimball Group
https://www.kimballgroup.com/category/articles-design-tips/
Kimball Toolkit Books on Data Warehousing and Business Intelligence
https://www.kimballgroup.com/data-warehouse-business-intelligence-resources/books/
November 24, 2021 at 2:52 pm
In Microsoft Power BI, best practice is to separate Date and Time to make model size smaller. Optimizing High Cardinality Columns in VertiPaq - SQLBI https://www.sqlbi.com/articles/optimizing-high-cardinality-columns-in-vertipaq/
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply