>> Next up, a collection of
learning sessions from our Power BI Women community.
>> We all know good data modeling is essential
to effective communication in Power BI.
Next up, we have Kelly Broekstra, who's
an Information Management, Business Intelligence,
and Project Management Specialist with
extensive data warehouse development experience,
and who has a passion
for understanding clients' needs
to create the best solution achievable.
Kelly's going to speak to us in this session about how
different data modeling decisions affect your reports.
[MUSIC]
>> Hi. Thanks for that awesome intro.
Yes, I'm Kelly.
I'm based in Melbourne, Australia.
I'm a mom, a spouse, a grandma,
a volunteer at a local youth group,
and I spend most of my working times as
a Business Intelligence Solutions Specialist.
Today, I want to talk to you about
data models and how to effectively communicate with
the right model within Power BI so
that you get the best communication out of that model.
Now, when I say effective communication,
I mean answering the business's questions quickly and accurately,
making the model that everyone can understand, not just you.
Firstly, I want to clarify,
I'm not talking about this type of model,
I'm talking about this type of model,
a Power BI model.
Now, those of us that have been
working in Power BI for quite a while,
we'd all be very familiar with the flat file model.
You've got a single table and it's
a connection to Excel or a connection to
a source system and all it's coming through is a single table.
It's great. You don't need to create relationships,
you don't need to worry about
many-to-many or one-to-many cardinality,
you don't need to worry about cross filter direction,
you have all the data there, and away you go.
Power BI compresses that data quite well,
it's like every port performs,
everyone gets the data they need,
and it's all wonderful.
Unfortunately, more often than not,
your boss or someone else from the business will
come to you at some point and they'll want more.
They'll always want more.
They want you to add data from another spreadsheet or from
another system, and after not too long,
you'll have something that looks more like this.
Where's the happy medium?
The medium between the single flat table
and the crazy star schema,
which I hope nobody actually has in their environment.
It's crazy. What we're actually aiming for
is a star schema and I hope
most people have heard that term before.
Now, before you go out and buy the book
and read the whole thing because it's very chunky,
which like I said, no one really has time to do,
let me give you a few basics.
A star schema looks like a star, like the one that we see here.
The fact table is in the middle.
The fact table needs to represent a process or
something in your business that has happened.
Not just the data that's coming through the system,
it needs to actually model something that has happened or or
a process or a workflow within your organization.
Its actions, its things that have occurred.
It includes with it numbers,
including some aggregate,
finding [inaudible] ,
do calculations on that kind of thing.
The fact table also has everything
that links through to the dimensions.
The dimensions are the points on your star.
They're the nouns, they're the descriptions,
they're what you filter things
by, and they're what you tell your stories with.
They sit on the outside and you can
have as many of those as you need.
Dimensions, most people understand.
Everyone puts their data in dimensions and it all works.
It's with fact tables that I find people get more confused about.
Just to clarify a few things
and give you a few things to consider,
fact tables, you can have more than one fact table in the model.
I quite often have conversations with people in Power BI.
They have one beautiful fact table that tries to do
absolutely everything and that's it.
You can have multiple fact tables that compliment each other.
Each fact table should represent either a different process or
a different action within the bigger process,
or they can have different grains.
A good example would be a sales system.
An invoice little grains,
so where you have a line per invoice item,
might have a customer ID,
an invoice state,
the number of things that were purchased,
maybe something like that.
That's one fact table.
You might have a second fact table in your model that talks more
to the products that were purchased on each of those invoices.
The grain to that would be the product line
as opposed to be the full invoice.
That's an example where you'd have
two complimentary fact tables that
work really well to get into describing
the overall business process within your model.
So you can always have more than one fact table
within a star schema.
Just make sure that you link your fact tables to three dimensions.
Don't try and create
relationships from a fact table to a fact table.
It tends to get a bit messier and not perform as well,
but you can have multiple fact tables.
The other thing that I do want to cover is there's
actually three different types of fact tables.
The one that we see most often
that we get from most systems
nowadays is a transactional fact table.
We have one line for a transaction.
A sales item is a really good example or
a transaction might be a higher or a promotion in a HR system.
It represents one line and that line generally doesn't change.
It comes through from the source system.
Once it's in, it does not change.
It's happened, we're not going to go back and change
the number of items that have been purchased, they're done.
That's the transactional fact table,
that's what we see most often.
The second type of fact table which does actually still
have a big use is an accumulating snapshot table.
This is where you're modeling a process or
workflow and that particular item does certain things on
certain days so you can have different dates stored
within your fact table to represent different days.
I'll show you an example shortly.
The third example is a periodic snapshot, and that looks more
at snapshotting what's happened at a specific point in time,
where that point in time is a regular occurrence.
A good example is a bank statement.
The periodic snapshot will have a line for customer 1,
2, 3, 4, 5,
the statement date,
might be the 1st of January,
and an opening balance and a closing balance.
Then on the second line for customer 1, 2, 3, 4, 5,
the 1st of February for the statement date and
then have an opening balance and a closing balance.
It gives you that ability to see the opening balance
and closing balance really quickly without having
to sum up the whole history of
the transactions that could have happened in that system.
But don't sum it together,
don't add all your closing balances for the year.
You'll be rich, but it's not
actually true indication of what's happening.
Now I found a really nice image that describes this from a book.
That book's just down there.
I've never really found the image online so I took a photo.
This one is not the greatest quality, but it's
a good visual representation of the three types of fact tables.
They're discrete or transactional
fact table working as single items.
The evolving fact table where somebody might move along.
A process, an order,
shipment, delivery, payment type process might have happened.
Then a recurring fact table where everything
works on a very regular recurring type basis.
I don't have much time left.
We're not here all day so I'm not going to show
you how to create the model with the star schema.
I'm not going to show you how to change your existing model
into a star schema, and I'm not going to talk much to
how a lot of this is done in Power Query or
in your ETL process and how you need to manipulate your data.
What I do want to do though, is talk
through a couple more examples.
I've spoken a little bit to the sale system
which a lot of people have so
it tends to come up quite often in conversations.
Customer buys a product in
store and the store wants to know the most popular products,
how much money they have made,
markup [inaudible] , which customers buy the most products,
the time of day that's the busiest,
those kinds of questions.
In that scenario, they're one-off events.
They are events that happen and
then nothing else happens afterwards.
The customer, very rarely,
are seen and returns that product,
but that would be a different process.
You want a transactional fact table where you have
one line for everything that's happened whether
that's a product line item or an invoice item,
depends on your business's requirements.
You then have dimensions of that event:
your customer, your store, your product,
you have the date dimension, and you
possibly even have a time dimension which would be
separate to your date dimension.
That would just list all the different times of the day
and that helps with analysis around what times of the day are
busier, and you can combine that with the date and work out
[inaudible] somewhere on Friday afternoon, for example.
Put [inaudible] at that time.
Make sure you've processed all of
the shipment things where everything's on the shelf ready to go.
Those things is the analysis that you can do with
a transactional fact table with
a separate date and time dimension.
Another scenario, a HR system for example.
A person is hired,
they're promoted, they leave your organization.
The manager wants to know the things like turnover rate,
different demographics around the people
that work within the organization,
how well someone takes generally before they get promoted,
what are their reasons for leaving,
those kinds of things.
Once again, you could do this as an evolving,
as an accumulating snapshot fact table where a person is hired,
they're promoted, they leave.
But an accumulating snapshot doesn't give you a lot of
room for they get promoted multiple times.
Unless you've got a finite number
of times someone can get promoted,
the accumulating snapshot model doesn't work quite as
well, but what does work though is the transactional fact table.
It's just a little bit more
difficult to do the calculations around
how long has someone stayed in
that position before they got promoted or before they left.
Think about the kind of questions that your business is asking and
build the fact tables around what you need to answer.
This is a really good example of where you might
have different types of fact tables.
You might have a transactional fact table,
one for hires, one for promotions,
one for terminations, and then you might actually have
an accumulating snapshot table that just
describes the summary of the history of that particular employee.
The third scenario or example I want to
speak to is one I get quite frequently,
which is an inventory or
a stock analysis, or anything to tell me the workflow
basically, but I'll use the stock example as we discuss this.
Stock item is ordered,
it's received at the warehouse,
it enters the warehouse,
it's then purchased by a customer online,
it's packaged, and then shipped out.
I'll show you some pictures.
You can do that as an accumulating snapshot like this.
Where you have the stock item and you have
the dates so that across
the top and you list out all the different dates
and then you can do a calculation of
the number of days in each phase
or you can have a transactional fact table
which is more of for this particular stock item.
It was ordered, it was received,
it hit the warehouse, and this were the dates.
You don't need to go and update things,
this just list things out, but it does
make it a little bit harder to do reporting.
I can record on a single item and see what it does, but
if I want to know how many items are currently in my warehouse,
I need to search for everything that has a warehouse state of
before today or whatever data I'm looking with this report for.
The data also going to join the things that haven't been purchased
so the text that I'd need here
would be a lot more complicated.
Whereas in my accumulating snapshot,
it's quite easy to just filter and
say all I need to do is drop on count of my stock items
and filter by where the warehouse state has already happened,
but the purchase state hasn't, and that
gives me the number of items I currently have in stock.
I can still see which items
have been in stock for the longest and can see
all the dates associated with them, and I
can drag and drop a lot more stuff.
The difference between the two models,
this one is very much,
"I can just drag things on,
I can produce my reports and answer questions very quickly."
The transactional fact table,
I need to write text and some
of the text can get quite complicated because I'm
trying to join on the other items within
the table that do relate, but also don't relate.
So there's a lot of remove filters
and complexity in those stocks calculations.
I just wanted to sum up.
Thinking about what you're trying to model will really help
you with what you're trying to build.
Think about the business requirements,
what are you trying to solve, and
what questions you are actually being asked.
Model real-life business don't just
model the source data that you've
got and where that's the only way I can do it.
You really want to make sure that you're modeling
your real-life scenarios
so that you can answer the real-life questions.
Now, some of this advice should make
your reports faster, smaller, and more scalable.
Sometimes not the case, but that's the idea.
But more importantly, they make it a lot
easier for you and for anyone else at
your business to interpret the model,
play with the idea and visuals,
add things to the visuals.
You don't need to know complex stacks to get all that working.
That's what we're trying to achieve.
I hope you've learned something today from
this session about how to model your data a little bit better.
Stick around for the next sessions,
they look really interesting.
I can't wait and thanks for listening.
>> Thank you, Kelly, for sharing your insights into
effective Power BI modeling techniques.