Data Modeling
SU-780(3 days)
Data Modeling
14.400 DKK
(excl. VAT)
Course calendar
Description
Data models are structures in which the business’s data can be recorded/collected and then analyzed. The thinking behind the two models is highly useful for BI developers, data analysts, report designers, programmers and app developers, as well as database administrators (DBAs). The course is not product-specific and is aimed at users of, for example, Microsoft SQL Server, Power BI, Excel Power Pivot, Analysis Services, Oracle, IBM Db2, PostgreSQL, MySQL, MariaDB, Targit, Qlik and others.
The course starts with a thorough introduction to the thinking and key concepts of relational modeling. We work on the transition from the reality of the business to recording/selecting relevant data in a data model. The thinking and the questions asked along the way are absolutely central to a successful database model.
The relational data model underlies the ‘classic SQL databases’ and is the most widespread data model in SQL database servers.
The dimensional data model is used for analyzing and presenting data, often from multiple source systems. The course gives a thorough introduction to the 4 phases of designing a dimensional model, including the transition from interview rounds to a finished model. Many pitfalls can be avoided by knowing the underlying thinking, and it becomes significantly easier to translate end users’ requirements and wishes into a living, usable data model.
Dimensional models are used especially in two areas: data warehouses/data marts and reporting, e.g. Power BI, Excel Power Pivot, Targit, Tableau and Qlik. Microsoft uses the dimensional data model in both variants of Analysis Services (Multidimensional and Tabular).
A well-thought-out database model is an important prerequisite for getting technical assistance with implementing the physical database, including communicating with external consultants. This applies to both the relational and the dimensional data model.
The course is for participants who want:
- To get a firm grasp of the key concepts and thinking in the two data models.
- To be able to “decode” and navigate existing databases in the business and translate from an existing database into the language of the business.
- To be able to speak ‘database language’, that is, ‘translate’ back and forth between the business and the database model, including formulating change requests to the internal IT department more precisely. This is also a big advantage when searching for information online!
- To strengthen their collaboration with external consultants in development and Business Intelligence and become more precise in formulating the business’s wishes.
- To be able to ask better-informed questions about the consultants’ thinking, strategies and documentation.
The course includes many examples, counterexamples and exercises throughout.
Prerequisites
To get the most out of the course, we recommend experience or knowledge equivalent to:
Next steps
If you want to build on your skills, these may be relevant:
Certification bundles
Module overview
Entities - the starting point for the design
- Identifying the entities in the real-world business - many examples
4 types of entities - when they are used, how they are identified
Identifying entities
- Primary keys - why it is important to get them right
- Surrogate keys - what their function is, when they are created
Attributes
- Nullability - NULLs in databases - meaning and use
Uniqueness
- When uniqueness matters in a database design
- When rows in tables are unique - and when they are not
Roles
- Handling roles in the database design
- The relationship between roles and tables
Foreign keys
- The point of foreign keys
- Composite keys
Binary relationships - when two entities play a role together
- One-to-many, many-to-many and one-to-one relationships
- How binary roles are implemented
Composite roles - when more than two entities play a role together
- How these roles are implemented
Check constraints
- What can they do, and when are they used?
Data redundancy - what is the point
1st and 2nd normal form
What normalization offers - with many examples
Opting for denormalization
Database server vs. client
4-part naming and dot notation
Requirements for tables
Good to know about tables
Good to know about data types
Examples of different SQL syntax for creating tables
How it differs from a relational design
- Dimensions and facts
- Data warehouses
The four steps in dimensional modeling
- From business reporting needs to a dimensional model
- Best practices for dimensional models
- Ongoing examples throughout
Dimension tables
- The three types of keys: Natural/Business Key, Dimension Primary Key, Dimension Key
- Nullability
- Hierarchies
- Denormalization
- Outriggers
- Best practices with examples throughout
Many-to-many relationships
- Methods for handling many-to-many relationships
- Examples throughout
Fact tables
- Fact keys
- Nullability
- Uniqueness
Fact tables - the three main types
- Transactional fact table
- Periodic fact table
- Accumulating fact table
- The relationship between the fact table types
- Ongoing examples throughout
The ETL process - Extract, Transform, Load
- The thinking behind populating the dimensional model
Slowly Changing Dimensions (SCD)
- Recording historical changes to attributes
- Introduction to SCD type 0 + 1 + 2 + 3 + 6
- Supernatural keys
- The 4 keys in SCD - how and when they are unique
- Considerations to include in the design of the ETL process to meet the requirements for implementing SCD
- Examples of SCD throughout
Free course advice
We care a lot about helping you find the course path that creates the most value for you and your workplace. Get in touch with our course advisors — they're ready to help you, free of charge and with no obligation.
I can't give anything less than 5 stars across the board. Either you're truly in a class of your own, or I've just been to the wrong course venues before. Fantastic place and atmosphere.... once you've learned to find your way around :-)
Your instructors are exceptional; skilled, competent and good at teaching - so you have everything you need to get off to a good start.
You have fantastic facilities, good food and lovely people throughout the building.
A really good, in-depth course. Your course facilities at Karlebogaard are nothing short of fantastic. A beautiful historic house with lots of fun stories and great course rooms. The course itself was very useful. I learned everything I had hoped to and then some. My instructor was damn good and really funny. He made it a pleasure, and the hours flew by.
Probably the best course I've ever taken and the best instructor I've ever had!! Really good depth and thorough explanations, and on top of that, fantastic food!!!
Delicious food, cozy facilities, good structure and atmosphere. I'll be back when I can.
Absolutely the best course I've ever taken!
The course venue is so inviting and welcoming that it has been a pleasure to be here. Clean, tidy and full of charm. I attended a course where all the individual parts came together into a greater whole that can hardly be described.
I got a lot out of the course. It opened my eyes to possibilities I didn't know existed. I'm sure this won't be the last time we're in touch with SuperUsers.
I never in my wildest dreams imagined that a course venue could be so beautiful. I don't know if it's true, but I have a feeling that the surroundings rub off on the people who work here, so everyone seems incredibly happy.
There's a really great atmosphere. All the way from the woman at the reception desk to the people who work in the kitchen.
The instructor tailored the course to my level to a very large extent and was flexible. I had very high expectations, and they were exceeded.
Great precision, good exercises, good pace, a great atmosphere and maximum learning.
The instructor was very pleasant and the exercises were extremely good. I really learned a lot about the topics the course covered.
On top of that, really nice, calm and green surroundings with good catering - especially good food. I would like to take my courses here again next time.
It was a real pleasure to take a course at SuperUsers. The week we spent on the course was worth the money and means that we can now save many consultant hours. It's always nice to have the knowledge in-house.
There is a relaxed atmosphere in the classroom, lovely surroundings in the building itself, and skilled instructors who do a really good job.
I started using all the knowledge I soaked up on the course almost right away, and now we are well underway with developing our future cloud solution.
There is no doubt about who we will turn to if we need other courses.
This is my first time at SuperUsers. It has been a really good experience. The instructor seems very experienced and competent.
The instructor's strong technical background made the course and the in-depth answers to questions a good experience.
A really nice course venue in pleasant surroundings that make for a good setting for focused learning. The instructor came across as well prepared, with extensive knowledge of the relevant topics.
The instructor also showed a strong ability to answer any questions that came up during the course.
I'll be back the next time I need to take a course. It's a lovely place, the food is fantastic, and the instructor is very knowledgeable and happy to share that knowledge!
The technical depth of the course was really good. The instructor's knowledge went much deeper than the curriculum necessarily requires.
You could throw all kinds of curveballs at the instructor, and he had it all under control - he also made the content exciting.
We have now completed Microsoft 365 courses for a total of 5 people here in our department. Everyone agrees that it was an excellent course with a skilled instructor and communicator who could discuss and explain things at the right level, while also being efficient and bringing humor.
It has been a really good experience.
It's calm and quiet at SuperUsers' country venue. Good atmosphere and catering. Some thought has gone into healthy eating when it comes to food and cake, so it doesn't get completely out of hand.
The instructor is knowledgeable about the topics and readily puts them in a broader perspective by relating them to other relevant areas. It's rewarding that this is possible too, and it gives you ideas for further study.
In addition, the instructor was engaged and entertaining to have presenting the content.
As always, it was a great experience, and you learn a lot in a short time from some very skilled instructors.
I work in Azure almost every day, and both courses have been really good at helping me understand Azure better.
The instructor is very presentation-oriented and gets you involved in the teaching and the material. He is good at varying the teaching so it never gets monotonous.
It is clear that the instructor has hands-on experience and not just the theory, with many good examples that related to real-world challenges we might encounter.
Good facilities and good food, without drowning in unhealthy habits.
Very good course, the instructor was the best. I've been here at SuperUsers before, now I'm here again, and hopefully coming back another time.
The instructor seemed very competent and has extensive knowledge of his field. He was good at explaining things in a clear and humorous way. There were also simple exercises that gave a good understanding.
The instructor was really good at going in depth while still making sure everyone was keeping up. A really nice venue and room, and great food.
There was no doubt that the instructor knew exactly what he was talking about, and even the simplest questions were answered gladly, without anyone being made to feel stupid.
A lovely, cozy place where the receptionist, the kitchen and the instructors make you feel at home. A good setting makes for good learning. Really good food that helps round out the whole experience of taking a course at SuperUsers.
Really happy with the venue and the stay over in the horse stable. I'll see if I can come back here for the next course I take. A really beautiful building, and great to experience a place like this. A fantastic place to hold a course.
