We all make mistakes in life. Who hasn’t forgotten to hit mute during a Zoom call before telling the kids to pipe down? Small errors can often be rectified. But when mistakes appear in reported numbers, the consequences can seriously impact your company’s bottom line.
If you are lucky, wonky looking numbers will alert you to a problem and you can QA your way back to the root cause before the report goes out. But what about when you don’t even realize the numbers are incorrect?
In this on-demand webinar, we cover four reporting operations, common to all BI tools, where mistakes often hide
- Model joins
- Default aggregation
- Filtering on aggregates
- Order of operations
Demo’ing across Cognos, Power BI and Tableau, we show you how to spot and avoid these gotchas that can seriously undermine your reporting efforts and cost your business.
Ensure your numbers are right out the gate.
Presenter
Patrick Powers
Trainer and Consultant
Senturus, Inc.
Patrick is one of our most popular instructors, regularly receiving high marks from students for his subject matter knowledge, clarity of communication and ability to infuse fun into classwork. Patrick has over 25 years of experience in data science, business intelligence and data analytics and is fluent across multiple BI platforms. He is a Tableau Certified Associate and well versed in Power BI. An expert in Cognos, his product experience goes back to version 6. He has extensive experience in Actuate, Hyperion and Business Objects. Patrick is certified in multiple programming languages including Java and C++, and database certification (MS SQL).
Read moreMachine transcript
Welcome to the latest installment of the Senturus Knowledge series. Today, we’ll be presenting on the topic of four simple mistakes that equate to big reporting errors.
0:16
Before we get into the heart of the presentation, a couple of housekeeping items.
0:21
Feel free to use the GoToWebinar control panel to help make the session interactive. While we have everyone’s microphones muted out of courtesy to our presenter, we strongly encourage you to enter any questions in the question panel of the GoToWebinar control panel, which you see displayed in front of you.
0:38
And Patrick, our presenter today, generally responds to questions as they are asked.
0:43
If we don’t reply immediately will cover it either in the Q and A section, or if we run out of time, will provide a written response document that we’ll post on our website at Senturus.com.
0:53
Speaking of Senturus.com, we always get the question, can I have a copy of the presentation? And the answer is, absolutely.
1:00
It should be up, on Senturus.com.
1:03
if you go to the Resources page and the Knowledge Center and click on Knowledge Center renamed from the Resource Library.
1:13
Make sure you bookmark that, it has lots of great assets.
1:20
And we’ve also put a link to the presentation in the GoToWebinar control panel in the chat section.
1:27
Today’s agenda, we’ll do some brief introductions of our presenter.
1:32
And then, we’ll get into the heart of the presentation discussing these issues that can cause you problems around modelling, aggregation, aggregate filtering, and order of operations.
1:43
Patrick’s also going to wear a bunch of hats today and jumps around between Cognos and Power BI and Tableau.
1:50
So, prepare to be amazed. And then we’ll do a brief Senturus overview and show you some great additional free resources.
1:58
And stick around. Make sure you stick around for the Q&A at the end for any ancillary questions that we have left.
2:05
So, introductions, pleased to be joined today by my colleague, Patrick Powers for the last thing, standing between him and vacation. He has 22 years of experience in BI and data analytics.
2:18
He is one of our trainers and consultants. He delivers classes on Power BI, Tableau, and Cognos.
2:24
And he has multiple certifications in programming languages and databases. And, my name is Michael Weinhauer, and I’m a director at Senturus. Among my various roles, I have the pleasure of hosting our Knowledge series webinars.
2:39
So, with that, I hand the microphone to Mr. Powers.
2:46
Hello. Thank you, Mike. All right. Hey, so I’m looking at this attendee list. Hello, everybody that I know.
2:54
I see you all out there, I see all your names.
2:57
You just come because you can’t get enough of my voice. Is that what it is? I know that’s what it is.
3:32
The things I’m going to show you today are some we’ve all experienced.
3:40
If you say you’ve never experienced this and you’ve only been doing it for like 30 sec, when you think you might have bad data.
3:47
Because the data in a report, in a visualization, it doesn’t tie back.
3:54
It doesn’t tie back to that source, and somebody keeps saying, these are the numbers I expect.
4:00
And immediately, we think we have bad data, but sometimes it’s not bad data.
4:07
Sometimes, it’s happening at a tool level.
4:12
And a lot of these things really do come from just experience and having to deal with this over and over and over again.
4:21
And, here’s the other thing, as Mike said, I’m going to be jumping between tools today.
4:28
This does not affect, this is not a Cognos issue. This is not a Tableau issue.
4:35
This is a business intelligence issue.
4:38
Any kind of tool where you have aggregated data, any kind of tool where you have joins and the first one, the bottling one, any place where you’re trying to work off of a transactional source can cause you issues.
4:55
So let’s start.
5:00
One of the biggest problems is incorrect joins.
5:03
No matter what you do, you’ve got to join. That’s wrong. And that could be something simple, look.
5:09
That could be you have customer ID in one table and customer ID and another table, but they’re crossing schemas. The two schemas were built 10 years apart and customer ID, it ain’t the same customer ID in the other table.
5:25
Do the names match? Yeah.
5:28
But the fields don’t.
5:30
This is not bad data.
5:31
It’s just different data.
5:34
The other issue is, when you’ve got carnality problems.
5:39
You’ve got data at different granular levels, You’ve got a sales table, and a returns table.
5:47
Well, guess what, a 1 to 1 isn’t going to work on that.
5:50
Plain and simple, because not every sale has a return.
5:53
And if you try to do a 1 to 1, you’re not going to get the right answers, but then there’s more nuanced things.
6:00
If I have 31 days of sales, but I only have 17 days worth of records for returns and I want to calculate out my average returns, what am I counting?
6:14
Am I counting 31 days? Am I counting 17 days?
6:17
Am I doing an outer join on that to get what I want?
6:20
Am I doing a blend so I can get what I want?
6:24
These are the kinds of things that can throw data off.
6:27
It’s not intentional, It’s not something you set out to do, at least I hope so, although I don’t know. I’ve got my eye on these, you out there, see I’m going to call yeah out. I see all your names I know, yeah.
6:40
OK, I’m going to call yeah.
6:42
Make sure you know that I’m paying attention Teresa says CEO.
6:47
I hope you’re all doing well too, by the way.
6:49
It is a Friday and yes, I am going on vacation when this is done.
6:54
Let’s talk about this. Let’s talk and see what happens when we have this. So, our first demo.
7:00
It’s going to be in Power BI and Tableau.
7:02
OK? That’s where I’m going to start off.
7:05
Now, I’ve pre generated a lot of these things to make sure that we have enough time for everything. I’m going through a lot of demos today again.
7:16
But this first one.
7:19
Here’s exactly what we just talked about.
7:23
I’ve got an orders table. I’ve going to return stable.
7:29
When I join correctly, I see what I want.
7:33
I see every order that has a return.
7:39
What happens if this joint isn’t correct?
7:42
Now, here, is where modern day tools are kind of nice, because a modern day tool won’t actually let you do it.
7:52
Oh, look at that, it won’t even let you make this mistake, which is really, really nice.
7:59
But I know, for a fact, there are a few of you out there writing your own custom SQL, and you’re dropping custom SQL onto these tools.
8:09
And, well, I, nobody’s going to check your Mac!
8:14
But you can still mess up.
8:18
You can still mess up if I set this too many to many.
8:22
And if I say crossville, turn both directions.
8:29
Look what happens to my data.
8:34
Where am I sales without returns?
8:40
Maybe this is what you want.
8:42
Odds are, it’s probably not, Then there are things where I can do a single.
8:49
and now again, though, hey, look.
8:51
Not sure if you really want to do this.
8:55
Still get the wrong result.
8:59
These are very easy mistake to make, especially when you’re new like this stuff.
9:04
Again, luckily, luckily, a lot of today’s tools stop us from making these mistakes.
9:11
Thank goodness.
9:13
But sometimes, we don’t get that luxury.
9:17
OK, let’s take a look at same similar thing in Tableau.
9:23
Here I’ve got orders.
9:27
And I’ve got, Let’s, let’s drop this table real quick, which is not.
9:34
If I set this to an inner join.
9:39
Notice that my rose drops to 800.
9:42
If I remove this, I remove this table completely.
9:47
I have 9994 rows.
9:53
But if I accept the default of an inner joy?
10:00
800 rows, because of exactly what I just said, not every sale has a return. Not every order has a return.
10:09
So we’ve got to know what we’re doing here.
10:11
Do I want a weft do I want to write, Do I want a full outer?
10:17
So, what happens if I am if I leave it as an image?
10:21
I only get those rows where there’s a return.
10:27
In this case, I get 296 rows.
10:32
What happens if I make it a left?
10:35
Now I get 5009 rows, and I get the ones that do have a return.
10:45
I bet if I change it, all right.
10:48
Back to the 296, basically, doing the same thing as if I had an Enter.
10:57
What happens if I do the devil? I mean, a full ladder.
11:03
I get my correct rows 5009, but now, my value show is null.
11:09
So, you may be saying, OK, if the full adder and the left outer are give me the same thing, which they technically do?
11:18
What’s the problem of using the fodder isn’t that safer?
11:20
Now, because a full outer is going to have severe performance impacts, potentially, and severe outer could, in fact, drag everything to a halt and really cause you problems by a million rows. I’m working off of 9994 rows here, people.
11:38
I’m not working off of million row dataset.
11:41
But if I was and I tried to do a full outer join, I think you all have enough experience.
11:46
Hopefully that you know, full, outer join is bad.
11:51
We’re not going to do Cartesian products over here.
11:55
So, you see something as simple as a joint can cause our data not to tie back.
12:06
This one, this one’s pretty obvious.
12:09
And it’s also pretty straightforward and direct to fix. It doesn’t take much effort to, oh, shoot, I only got 800 rows. I need to change my join.
12:22
What about more nuanced issues?
12:25
What about things that are a little more difficult to see?
12:31
Aggregation, especially when I’ve got the different default levels of aggregation, well, I’ve got different granularity levels or when my upstream Modeller has set it to something different.
12:50
What kind of problems is that going to cause?
12:53
For example, in Cognos World, if my framework Manager Modeller sets unit price, they might set it to a total not realizing it should be an average.
13:07
Or, there could be different business reasons.
13:10
There may be some who do need to see it as a total versus an average.
13:15
So, you may end up having to publish multiple things out to your or, to your models, Again, whether it’s Tableau, whether it’s Power BI, whether it’s Cognos.
13:28
Aggregation or aggravation can always be an issue.
13:34
Let’s take a look.
13:35
Let’s take a look at that in Cognos.
13:42
As I said, I pre ran a bunch of these things.
13:46
So, for this, I’ve created a little data module.
13:53
I’m going to open up this report right here.
13:58
Here you see the Revenue column with three different aggregations.
14:06
Obviously, in the real world, it would not say revenue, average revenue, not just say revenue.
14:12
Let’s see what this impact adds.
14:15
At this granular level, at the order number, product, line, product, type, product, name, level, My revenue column is equal to the other two columns.
14:27
Great.
14:29
And if you’re using this to spot check your data.
14:32
And if you’re using this to say, yeah, revenue looks good, you’re fine.
14:38
But we’re moving into self-service these days, aren’t we?
14:41
We’re trying to figure out how to give people their own dashboards.
14:45
We’re trying to figure out how to get people to do their own things.
14:49
And just because this works here, let’s see what happens when I start taking columns out.
14:59
Look at that.
15:01
I go one different granular level.
15:08
The ones in green?
15:11
No longer match.
15:14
These numbers no longer line up, OK?
15:19
And the hard part is, if you’re spot checking numbers, and you see the rest of these, that all look OK, you may never see this.
15:32
You may never notice that the that you’re at a different rate new level.
15:36
Now it gets worse, the lower I get and here it starts to become a little more clear that, oh crud, there’s a problem with my aggregation.
15:48
I go all the way down.
15:53
Yeah, OK, now I can see it.
15:56
Now I can see that there’s a problem.
15:59
But again, if we’re trying to do this self-service, we don’t know what they’re going to bring. They overdo it.
16:05
What if they just brought over order number?
16:09
Oh, luck.
16:12
We’re back up off.
16:14
What about that?
16:17
There may get that look, we’re doing great. If I’m spot checking orders.
16:25
There’s no problem with this first 30 or 40 orders, then I get down to here.
16:33
So maybe like 100 beekman.
16:37
That one order the aggregations rong and if that one order is the one that your end customer cares about, they’re going to come after you and say, Why does this number look? so inflated?
16:50
There’s another one.
16:53
So, out of 200 rows of data that I’m showing right now, 3, three, data items are actually incorrect.
17:05
You can’t beat yourself up for missing this sort of thing.
17:09
It’s not always obvious when there’s three out of two hundred.
17:17
Doesn’t make them right, though.
17:19
There’s still an aggregation issue.
17:22
And, again, thinking of self-service thinking of end users, thinking of people who are non technical in nature, they’re not going to understand what’s going on.
17:33
Let’s look at another commerce issue.
17:36
This is always one of my favourite things to look at because nobody ever catches this.
17:43
Any of you, Cognos folks?
17:47
You may have gotten nice and comfortable with using.
17:54
Or you come back toolbar.
17:56
You may have gotten nice and comfortable with using default summary.
18:02
Default summary is great.
18:03
Default summary looks at the aggregation of a column and calculates it correctly.
18:10
So if I do default summary on this contribution to total column, I’m going to get the actual right answer.
18:18
It’s not just going to total it up and I’m going to prove that to you.
18:27
Oh, That’s actually right, in this case, 100%.
18:35
So, this is added up. Everything looks good.
18:40
Now, what I can do is I can edit that.
18:46
I can edit that.
18:51
Drop in a different calculation.
18:59
Where I say.
19:04
Percentage.
19:07
Revenue.
19:09
four, country.
19:19
Gosh, darn it. That’s not what I wanted. I want a percent of revenue.
19:22
The other ones for country, that’s better, jammin vacation mode, There we go.
19:28
So, we see that Australia now is 1.87% of our total. We’re not using the default summary against this.
19:36
This is my percentage against the Grand total.
19:41
Let’s look at another issue.
19:44
Let’s say that I drop a new calculated field in here.
19:48
All right. I’m going to call this new calculated field bonus.
19:52
I’m just going to take us, take a little calculation.
19:56
Now, let’s say, hey, look, If their variance over their sales Target is greater than one million dollars, then subtract a million dollars from the variance.
20:06
And we’re going to give them a 1% bonus.
20:09
Cool. Very nice of us.
20:12
So Alice Walter made a $12,000 bonus cool, but I’ve got an empty box here.
20:22
Default Summer Cool, I got a number.
20:25
Wait a minute, 7, 12 plus 5 plus 7 plus 11.
20:33
doesn’t equal 73.
20:36
Sure, certainly doesn’t look right, 16 plus 27 plus 8, 75.
20:42
What the heck Default summary is actually doing it at the row level.
20:51
So what default summary is doing, is it saying 87 million, or, sorry, 79 million, minus the eight point three million, OK, That’s greater than a million subtract, one million from that.
21:06
That’s seven point three million.
21:09
That would be a $73,000 bonus.
21:13
But that doesn’t add up, does it?
21:16
Because in this case, we don’t want it to do a calculation.
21:20
In this case, we actually have two, say, no.
21:24
Now, don’t calculate that the same way.
21:29
Give me a total for that one.
21:32
And then I have to calculate the total out separately.
21:39
Now I get my right number, so sometimes these numbers are blended.
21:44
And sometimes you don’t see that there’s an actual problem.
21:50
You might not even have noticed that because everything else looked good.
21:56
And as a report authors, it’s not always your job to look at data like that, Right?
22:01
As report authors, your job is to build a report.
22:05
But you need to be able to spot check and spot for things like that.
22:11
So aggregation will cause you problems. I promise you.
22:15
It will cause you problems.
22:18
All right.
22:21
Our next issue of the day, filtering.
22:26
So we’ve looked at modelling problems, we’ve looked at aggregation problems.
22:32
Let’s look at filtering problems.
22:35
It’s important to know when we are filtering.
22:40
Are we looking at a row level, or are we looking at an aggregate level.
22:47
In the Congress world, this was known before or after aggregation, similar thing happens in Tableau. And a similar thing can happen in tax.
22:57
For this, I’m going to show you one of each, so you can see this applies across the board.
23:03
But here’s a good example.
23:05
I had two orders with printers, $175,000 in revenue, one with $85,000 in revenue, and I say filter revenue greater than 80,000.
23:17
When we filter, it is going to make a difference.
23:21
If I do it before I aggregate, then I’m throwing out the one order for 75.
23:28
If I do it after I aggregate, I’m showing a rolled up result of 160.
23:36
That’s a big difference, isn’t it?
23:39
Usually, it’s not that big of a difference in most cases, But here’s a good example of when, wow.
23:46
If I’m your end user and I’m expecting to see 160,000, and I see 75,000, that’s going to raise some flags and everybody’s going to go nuts and wonder what the heck happened, what did you do now?
24:02
So, let’s take a look at all three of these.
24:05
We’re going to look at a Power BI aggregate filter.
24:08
Before, and after that, I just described an aggregate filter issue in Tableau, OK?
24:13
Little something for everybody.
24:17
And yes, as Mike said, I jump between all three of these.
24:22
Oh, hey, I see more people I know who’ve joined. Hi.
24:27
So let’s go back to Power BI.
24:32
So here, wrong on this one.
24:35
Here, I’ve got total sales, total sales for each of my sales territory groups.
24:45
And what I want to do is I want to start calculating out some percentages.
24:51
And in order to calculate percentages, I need to create some decks.
24:55
So the first tax I created is one called Total Sales, All, where I’m calculating the total sales against all sales.
25:07
So this slide give me this number.
25:11
Let’s take a look.
25:16
So for each row, that’s the total sales.
25:19
So I can use that to divide and get my percentage.
25:24
Perfect.
25:25
So I’ve got total sales, all percentage of total, divide, total sales, by total sales, all.
25:31
Awesome.
25:35
And now I see that Europe was 30.4 thirty eight point seven hundred thirty point eight six.
25:43
All right, that’s that.
25:47
What happens when I start filtering, though?
25:50
I’ve got two slices on here, one for Product Group, one for you.
25:58
Interesting, my total sales all does not reflect that change.
26:05
My total sales changes, because my total sales is filtered by product group.
26:13
My total sales all, however, is not.
26:17
So, now, my percentages, once again, look screwy.
26:23
So what I need to do is create some more decks.
26:27
So what I’ve got for that, Total sales all selected.
26:36
So I’m going to calculate total sales against the selected sales.
26:47
Sure.
26:52
At the highest level, 29 million.
26:55
But when I filter, now I’m going to divide against 700,000.
27:02
So if I take total sales all percent selected a total, there we go.
27:12
So of clothing, Europe was 24.38.
27:17
Overall, it was zero point two eight when I look at just clothing. But I’m looking at clothing 24.3.
27:24
Well, what happens when we have multiple slices?
27:29
Now I want to take this down to the year level.
27:33
Yeah, nothing.
27:36
This sort of works.
27:47
Nothing there.
27:49
So I have no clothing there.
27:51
See what I have slicer is that can throw everything else off.
27:57
So guess what?
27:59
Total sales, all years, so now I’m calculating total sales against all years To get another number.
28:16
And total sales, all year’s percent of total.
28:22
And I can keep this going with territories and other things, which I’m going to show you in the next one.
28:27
But now, I’m getting more accurate numbers.
28:33
So knowing what we’re filtering on, knowing what we’re trying to show, knowing what is selected, is going to give us a whole bunch of different numbers. And there, therein lies the problem, right?
28:52
I’m going to show you this in the next one.
28:54
What numbers are correct?
28:58
And Tableau people may be familiar with this because I’m going to show essentially, I’m going to show you essentially a table calc, with direction and scope.
29:10
But I’m going to show to you on Power BI just to show that this impacts everything.
29:16
So here, I’ve got Sales Territory group, and I’ve got my sales for each year, and I’ve got a total for the rows, total for the columns.
29:27
Here it is.
29:29
Now, I’m going to copy and paste this matrix.
29:34
And for those of you who didn’t know, you can copy and paste visualizations and power BI. Well, there you go, you’re welcome.
29:41
What am I going to do?
29:43
So I’m going to get rid of total sales on that one.
29:47
And I’m going to replace it with total sales, all percent of Total.
29:52
Here I’ve created a pane table, calc, my pain equals 100% semi row total.
30:04
And my column total equal 100%.
30:11
Copy and paste it again.
30:18
Hello?
30:22
And on this one, on this one, I’m going to do a different one.
30:30
Total sales, all Territories.
30:34
Percentage though.
30:37
Now, each column equals 100%.
30:46
And I bet.
30:48
know what I’m going to do in the last one. Now.
30:52
Let’s take that last one.
30:55
Oh, look, Total Sales All Year per cent of total.
31:02
Same data, same layout, four different sets of results.
31:11
This one’s obviously different because it’s currency.
31:15
But these three.
31:19
Which one’s right?
31:21
All of them.
31:23
So knowing when you’re filtering, knowing on what you’re filtering is going to make a difference in that output.
31:31
Because unless you talk to your business users, unless you know the business question that they’re attempting to answer, you going to give them this. But they really wanted this except Bob who wanted this.
31:49
And again, those of you who are Tableau, folks, you should be familiar with. This is a table Calc Sculpin direction.
31:56
Here, though, same thing.
31:58
All depends on what I’m using to divide by.
32:06
All right. You’re all very quiet today.
32:09
Hopefully, somebody is at least gone. Oh, at least once. I hope so.
32:15
I don’t see any questions, which scares me.
32:19
But I do hope somebody at least maybe had a flash that explains why something’s looked weird to them.
32:29
Or you could just lie to me and tell me that Yeah, you’ll like it.
32:33
Let’s look at this in Cargoes, dude.
32:39
Got another one here, before and after auto aggregation.
32:48
This is the same issue I just described to you with the whole printer thing.
32:53
I’m filtering on my quantity.
32:59
Thank you for one.
33:02
I just wanted to know somebody’s actually listening to me out there. Thank you.
33:10
I worry that we’re all live.
33:16
I always worry.
33:17
Hello, Jennifer. Hello, Patty.
33:20
So what I’m doing here is I’m filtering.
33:26
And what I’ve got, for what I want to do, is I want to filter on quantity.
33:30
So let’s take a look at those.
33:32
I’m going to create a detail filter, and I’m going to base it on quantity.
33:38
I’m going to say where quantity is greater than 25.
33:48
Or quantity is greater than 25.
33:50
My default behaviour is after auto aggregation, mean, roll up everything for Australia 2011 Web, and then drop anything that’s less than 25.
34:10
No real changes, right?
34:12
Everything still looks pretty much the same.
34:15
What happens if I change that, though, before auto aggregation?
34:27
Those numbers changed just slightly.
34:32
Let’s look at that again.
34:38
So just, let’s just focus on this first one, 93,584, 9884, Penny. You are absolutely correct. In most cases, a higher number does make sense.
34:52
In most cases, what a user wants is after auto aggregation, but not always, because, again, look, if I change this member, one more time, 93, 584, 9319, not a huge difference.
35:11
OK, not a big difference that could go unnoticed.
35:17
But that could have ramifications for your business.
35:21
It’s a small nuance.
35:23
In this case, the difference is, is that it’s thrown out the record level transactions that we’re less than 25 in quantity, then it’s rolled everything up.
35:38
Jennifer, I’m out of 11 dot, two dot something.
35:43
You are corrected, the olden days before was the default, but somebody finally got smart.
35:50
I couldn’t pinpoint, you exactly want to change the 11.1 point 12, or something kind to thing.
35:57
I’ve notionally noticed that in 11.2 that it is now after is the default.
36:03
So you’re not, you’re not crazy.
36:07
It did used to be something you had to be careful about.
36:12
And, so, those of you who are still on older versions of Cognos, that is something you want to pay attention to because, in older versions before was the default.
36:24
And I can tell you in other times, I’d be doing demos and get no data return, because I forgot to change it.
36:35
Yeah, but 11 dot, what, Jennifer, that’s what I’m saying, and it was somewhere in the dots.
36:40
Which, just to make everybody feel old, Version 11 came out seven years ago, this December.
36:51
Yeah, seven years ago.
36:56
So if you’re a weapon dot, one year, year, and a little behind.
37:02
So, again, very nuanced, very small, but still makes an impact.
37:14
Tom, the question here is, do I see automation helping self-service users?
37:20
I do see some of the tools helping with some of these things.
37:25
Like I just showed, the default now is that the default in Cognos is now after auto aggregation. So, yeah, I do see some of the tools.
37:37
We solve this with modelling and Power BI, where there are where they’re not letting you make these mistakes, where they’re not letting you select joins that are invalid. So, hopefully, we will see more and more of that.
37:55
As far as I know flawed, it’s a regular 11 dot to Watson is technically built in to progress. There’s nothing special about this. I couldn’t tell you exactly what 11 dot 2 dot, what we’re on.
38:13
Login dot 2 dot 1.
38:15
So I think this is just a name change.
38:19
That’s all.
38:22
And yes, Patty dot seven is the one that we recommend for most folks that are not looking to go to two yet, but looking for long-term support. So yeah, there’s nothing wrong with being on 11 dot 1 dot 7. Don’t think I’m making anything wrong, but I’m just saying.
38:38
Watch for that aggregation.
38:42
Watch for that aggregation issue.
38:45
I want to show you where to 40 almost here, Mike, stressing, because I’m not a As far as I should be.
38:54
I don’t want to cause Mike stress.
38:57
Yeah, Maybe a little bit.
38:59
Let’s take a look at how this happens in Tableau.
39:05
All right.
39:05
So, what I have here is I have my ship mode.
39:09
And I’ve got my subcategories and this is my average profit, my average profit and I say, hey, show me average profit, greater than $50.
39:27
So, I’m going to put that in filters. I’m going to say average.
39:31
I’m going to say at least $50.
39:34
Woah, bam!
39:37
What the heck?
39:38
I went from 17 subcategories to seven.
39:45
But the business user comes back, says, No, no, no, no, no, wait a minute. Wait a minute!
39:48
Wait, I know we have profitable transactions for bookcases.
39:55
I know we do.
39:58
Yeah, sure we do. I’m rolling it up first and then I’m getting rid of it now. No, no, no, no, I want you to look at it at the transaction level.
40:06
I want you to get rid of transactions that are less than $50, Ah, OK.
40:15
So, now, what I can do in Tableau is, I can change this filter two a dimension.
40:22
And when I change it to a dimension, I’m telling it, look at look at the individual row.
40:35
Larisa, before I, before I finish this, I want to answer your question. Lisa asked, if report. So final numbers. What’s the common practice to validate results and by whom?
40:44
So, that’s what’s on your organization. My earlier point was that a lot of organizations, report writers are literally that they’re report writers.
40:53
They might have some business knowledge, but they are not necessarily the end user of a report.
41:01
And, so, they know enough to spot check and they know enough of the business like the Cognos, what I showed you earlier, where it’s very nuanced.
41:11
That would probably pass Spacek, and a report writer probably has the business knowledge to do that.
41:18
The wheel, the real final validation, needs to go through typically a business person who has that longer term business experience that on the ground, because watch what I’m about to do here when I change this to a dimension, and I say at least $50.
41:42
Look at that.
41:44
Look at the difference between the two reports, folks.
41:47
Here, I adapt to 16 subcategories.
41:51
and the difference here, this was blank a second ago.
41:55
A second ago, this had nothing in it.
41:58
because rolled up whoa, Bob, my average profit was not $50, but if I look at just profitable transactions, my average profit for bookcases as $121.
42:17
Again, two very different results, which one’s right?
42:22
Both of them.
42:24
It all depends on the business question so Larisa, or Larisa, your to your question who can validate that.
42:34
Well, as a report author, I can say, Yeah, that looks right to me, but you know what, so did this.
42:43
The key is having project managers or business liaisons who can come to you and say, hey, the business question is, show me my average profit for all transactions with greater than $50 in profit.
43:00
Thank you. There’s your results.
43:06
Now, Tom, back to yours about self-service.
43:10
That one, this is the kind of thing where, again, even in a self-service environment, they need to know the business question they’re asking, right?
43:19
And that’s the kind of thing that we help do with at Senturus, is that we’ve got folks who can understand the business question and the technical question, and we can help bridge that gap and be that liaison if you don’t have somebody who is that liaison.
43:38
So you saw aggregated and disaggregated in both tools.
43:42
You’re quite welcome where it’s 17 minutes to go, which means really, I have about seven minutes to go.
43:49
And my last one, order of operations, when do we do something?
43:57
And if you’re a SQL person, thinking about it in terms of a where clause, versus a having clause, a where clause is going to apply to an entire dataset, where this equals this.
44:14
A having clause is going to say, hey, go get this dataset first.
44:20
Then, of that data set, show me this.
44:25
Within tools, this is known as context, or solve, order, context, or solve order.
44:36
And so first thing I’m going to show you is how this works, in a communist world again.
44:44
Ah, beep, boop!
44:49
I have one more.
44:52
Oh, look! Solve order demo.
44:58
Right out of the gate.
45:00
Everybody with eyes.
45:02
Yeah, you with the eyes, you can see that my total row is wrong.
45:10
You can see my total rows wrong. I’m adding up my totals.
45:16
And I’m ending up with 204%, not what we want.
45:23
So how do we fix something like that?
45:26
This is what’s known as solve order.
45:29
Do this first, Then do this.
45:32
I know people who have gone their entire cognitive career and never once change the solve order.
45:40
Solve order works in cross tabs and charts.
45:45
I’m trying to make this better so you can see here.
45:48
It works in cross tabs and cross tabs and charts.
45:52
And this is basically, I’m just assigning a priority to this, I’m saying, Do this first.
45:58
Then, do this.
46:09
Look at that, and just say you saw, so I can show you that again.
46:15
I said, this has the higher priority, calculate this percentage before calculating this percentage.
46:26
That’s it. That’s all there is to it. And I would wager that out of 100 of you that are here, thank you for. It is. Out of the hundred of you that are there, there’s going to be at least one of you who was banging your head against the wall.
46:41
Try to figure out what that solve order was for, and what the heck you’re supposed to use it for.
46:47
So there it is, gang, don’t beat yourself up.
46:51
You could be using this for years, and, not know, some of these settings are there.
46:59
It literally took me seconds because if I turn that solve order off, I’m back to 204%.
47:10
All it is, do that seconds: OK?
47:18
Man, is that a kick in the you know what?
47:23
So, there it is in Cognos.
47:26
I got a couple minutes left.
47:27
So I want to show you something in Tableau that’s very similar context filtering in thank you, Penny.
47:35
Context filtering in Tableau.
47:39
This is always a beautiful one.
47:41
I’ve got accessories. I’ve got customer name.
47:44
I want to see my top five customers for each subcategory.
47:55
five, Hey, I’ve got a five.
47:59
They are, Or, is it?
48:07
Look very carefully at the customer name.
48:10
These are my top five customers.
48:13
Not my top five customers, my subcategory, Because the, these are independent filters, Filters are acted on independently, and you’re saying, well, ad subcategory, that it should work, but, yeah?
48:32
Whoops, what I wanted.
48:36
Fine, smarty pants.
48:38
Here you go.
48:40
Here’s subcategory, Guess what?
48:46
Each one of these filters is independent.
48:50
So when I go to Bookcases, I see nothing because none of my top five customers bought Bookcases, So what I have to tell Tableau to do, go get the subcategory first of the sub category, show me my top five.
49:13
And in the Tableau world, that’s known as context.
49:18
Add to context, what, bam, This is my top five now with in that subcategory.
49:30
And I know one of you is thinking, one of you is thinking, Well, we’ll go back to all.
49:39
In this situation, all still shows just the top five because I’m getting all subcategories. So I’m getting all five customers.
49:49
So how do you fix an issue like that?
49:52
There are different ways to fix it. You can use things like index. You can use things like rank.
49:57
So I’m going to quickly show you something else here.
50:01
What I’m going to do is I’m going to take another instance of sales to the label and then I’m going to change that into a rank.
50:09
I’m going to do a quick table Capcom rank, then I’m going to tell it to look at it as if it were a Do it paying down, compute paying down?
50:22
And then, lastly, I’m going to tell it look at it discreetly.
50:26
So, now I’ve got the discrete rank for each category and I can use this up here.
50:37
How cool is that?
50:39
Now, I can filter on that.
50:44
I can say show me my top five.
50:49
Boom.
50:50
There’s my top five for each of my categories.
50:56
And I can hide that.
51:01
So, there are ways around it.
51:03
By using things like index rank, creating calculated fields, the quick and dirty way, I can add to context, then I could hide the All.
51:17
So I can’t see the All, but this, this gives me a cleaner way when I have a smaller set of data.
51:25
251, I did my gosh darn best.
51:40
Sometimes it’s not the data.
51:43
Andrew, you give me enough time, I can get to the total for all other than top five.
51:47
Staff created a few calculated fields, and we’ve got nine minutes, can’t do it, But I could do it, OK? Sometimes, the issue isn’t where data is, how the tools interpreting the data.
52:00
Always look for joint and structural issues, first.
52:04
Especially if you’re using a transactional source. Transactional sources are notorious for problems.
52:11
I’m doing a live stream in March on YouTube.
52:15
We’re going to talk about data architecture, why? Even in today’s world, you still need a star schema?
52:29
You can go have your lake house, all you want, work with the lowest level granularity, roll it up.
52:36
See if you’ve got aggregation issues
52:39
Look at tabular models to make sure your raw results are right.
52:45
Then start working within your filtering.
52:48
Hey, approach it in a systematic way.
52:53
And you’ll find that sometimes it’s not bad data, It’s just the way the tools are implementing.
52:59
And with that, I’m handing it back to Mike.
53:04
Thanks, Patrick,, you can continue entering questions into the panel if you want us to run through those.
53:11
If we have a couple minutes left, at the end, but we also have some great free resources, hundreds of them on our website, and we’ve been committed to sharing our BI expertise for over a decade.
53:22
A couple months ago, Patrick presented on Power BI on Python, for example.
53:25
And you can find, although the, past webinar’s content on our resources page in the aforementioned Knowledge Center, which, again, you should bookmark.
53:36
And if you want to get more great stuff from Patrick or any of our other trainers, we do offer complete BI training in Power BI Cognos and Tableau.
53:48
As you can see, many of our instructors are bilingual and trilingual, even multilingual, and we offer all the different modalities that you might need, tailored group sessions, small group mentoring, instructor led, online courses, and self paced e-learning.
54:03
You can mix and match those to meet your organization’s needs.
54:06
Speaking of those, we do have several upcoming courses here.
54:11
You can see if you, I won’t read all these but everything from data modelling and power BI with Python and R, Cognos Class’s Tableau.
54:21
Head on over Senturus.com to training and look in the instructor led training section for schedules and to sign up.
54:29
Couple of quick slides about Senturus, at Senturus, we concentrate our expertise solely on business intelligence with a depth of knowledge across the entire BI stack.
54:38
Our clients know us for providing clarity from the chaos of complex business requirements, disparate data sources, and constantly moving targets, made a name for ourselves because of our strength in bridging the gap between IT and business users.
54:53
We deliver solutions that give you access to reliable analysis, ready data across the organization, so you can quickly and easily get answers at the point of impact, for the decisions you make and the actions you take.
55:05
Our consultants are leading experts in the field of analytics.
55:08
Years of pragmatic, real-world expertise, and experience adventuring, the state-of-the-art.
55:13
In fact, we are so confident in our team, and our methodologies that we back our projects with a 100% money back guarantee that is unique in the industry.
55:23
And we’ve been doing this for a long time over two decades.
55:26
We work across the spectrum from Fortune 500 companies down to the mid-market. No doubt, you recognize most, if not all of those logos. They’re solving business problems across all these different industries and functional areas, including the Office of Finance, Sales and Marketing, Manufacturing, Operations, HR, and IT.
55:42
Our team prides itself on being both large enough to meet all of your business analytics needs, yet small enough to provide personalized attention.
55:51
If you’d like to join the Senturus team, we are hiring talented and experienced professionals.
55:56
You can see, we’re looking for a senior Microsoft BI Architect, a Training Director, and a Java Software Engineer. So you can see the descriptions there at the link on, again, The Senturus website.
56:08
You can send your resume if applicable to jobs@Senturus.com.
56:13
And with that, Patrick, did we get any more questions you want to respond to before we wrap it up here?
56:21
We did, one of the biggest decisions you’re going to have to think about is how much of self-service are you looking to do? Whether it’s data modules, whether it’s dash boarding, whether it’s explorations 11 to they have really stepped up a lot of those self-service things.
56:40
And I would, encourage you to take Scott up on his 15 minutes.
56:51
So, look, there’s nothing wrong with the older version, of course.
56:57
But, hey, let’s get your modern, let’s get you going.
57:01
Let’s get you to the self-service portion of it, and let’s bring it to the future. And let’s get people doing this as good as they can.
57:10
So that and there you go.
57:12
I think, I doubt everything else, the question was, if someone was looking at an upgrade from 11 0, would you recommend going to 11 1 7 are going to 11 and 2.
57:20
So, that’s the context for that response.
57:25
Thank you very much. I think you did answer all the questions. So, with that, we can go to the last slide. Thank you. First and foremost to Patrick. Our presenter for another content, rich and entertaining presentation. And thank you to all of you for taking time out of your day to join us. If you have any business analytics needs, training needs, upgrade means migration needs. Please reach out to us at info@senturus.com. Or if you still actually use a telephone, you can reach us at 888 -601-6010. Thank you very much, and we look forward to seeing you on our next Knowledge series event.
