Like a beautifully plated meal, a beautifully presented dashboard is the result of careful preparation. Using clean, well-prepped data is an absolute requirement for making accurate projections and informed decisions. Cognos, Tableau and Power BI all provide ways to cleanse data, but the options, methods and approaches in each differ significantly.
In this on-demand webinar we put data preparation to the test with each of the big three analytics tools. We peek behind the scenes of effective visualizations and demo how data cleansing varies when using Power BI, Tableau and Cognos.
Get a better understanding of the capabilities available within the three tools, their pros and cons and how they measure up. We discuss and demo
- Cognos Framework Manager and data modules
- Tableau Prep Builder and Prep Conductor
- Power BI Power Query Editor
We’re not selling you on any particular platform. We are partnered with all three of the market leaders and are fluent across the platforms. This is your chance to get an unbiased tool comparison.
Presenter
Pat Powers
Trainer and Consultant
Senturus, Inc.
Pat is one of our most popular instructors, regularly receiving high marks from students for their subject matter knowledge, clarity of communication and ability to infuse fun into classwork. Pat has over 20 years of experience in data science, business intelligence and data analytics and is fluent across multiple BI platforms. They are a Tableau Certified Associate and well versed in Power BI. An expert in Cognos, their product experience goes back to version 6. Pat has extensive experience in Actuate, Hyperion and Business Objects and certifications in Java, Python, C++, Microsoft SQL.
Read moreMachine transcript
Hello, everybody, and welcome to another Senturus webinar. We’re going to be talking about data cleansing comparison between the three main tools, Power BI, Tableau and Cognos.
0:42
In the GoToWebinar control panel, please feel free to use that to make this session interactive. We do try to answer questions in line while the webinars in progress. Those of you out there, my fan club, you know how I try to do that? I pay attention to, I see you all out there. I see some names.
1:01
I recognize, I know you’re here for me.
1:05
It’s OK. I’m here for you, too.
1:07
So, say hello to me in the question panel.
1:10
Tell me you’re having a great day or not. If we don’t reply immediately, we will cover it in the Q and A section, or we will have a written response document that we’ll post on Senturus.com.
1:24
The first question we get is, Hey, can I get a link to the presentation?
1:29
Of course, you can, And I am going to put that into the chat window right now or into the end of the chat window.
1:41
There’s also going to be a chart, and I will be putting in a link to that chart in that window as well.
1:47
So, you’ll have access to it. There it is. You want to go to Senturus.com, select the Resource tab, and then in the Knowledge Center, or Click the link I just sent you.
2:02
Hey, wow, I now see, I think this is the very first time. There are two people out there with the same last name as me.
2:11
Wow, I know! Do I have relatives I don’t know about?
2:17
It wouldn’t surprise me.
2:27
On there, you will also find some great stuff with Senturus.com resources.
2:34
Our agenda. I’m going to be doing an introduction, because you all need to know who I am. Those of you who don’t know who I am, and then we’re going to talk about an overview of data cleansing. What do I actually mean, when I say data cleansing?
2:46
Then, we’re going to look at the three tools.
2:48
We’re going to look at Power BI and we’re going to look at Tableau, going to look at Cognos, then, again, there’s going to be a nice chart.
2:57
And, lastly, we’ll do some overview, and we’ll do some Q and A, We will run until about 3:00. I will be here.
3:11
Look at that, you guys are great, Jolene, how you do, and we love the state of South Dakota. Elizabeth, yes, of course, you have to register, we need your info, howitzer, we’re going to send you marketing stuff, come on.
3:25
And, John, how you deal. And I love that you guys are giving me some feedback in that you’re awake and paying attention.
3:32
I love it, it makes me happy, makes me feel like you actually care.
3:35
All right.
3:36
So speaking of that, who am I? Who is this weirdo talking to you, because I am pretty weird.
3:43
Pat Power, as data scientists, consultant, trainer, Senturus, Incorporated.
4:06
This is going to be my 26 the year of business intelligence data, analytics, data science, data cleansing, databases, data warehouses, whatever term du jour you wish to give and I’ve been doing it for almost three decades. I do a lot of the training, actually, I pretty much do all the training for Senturus.
4:26
I teach classes on Power BI, Tableau, and Cognos. I am certified and all of the above. I am also certifiable. I am certified in multiple programming languages, including Java C++. I’ve got my database certifications.
4:41
Really, I should just have a wall, just have a wall of plaques.
5:06
Hey, before we get into this, we’re going to take a quick poll. You know how we like our polls.
5:14
What are you using today? Because what platforms are you using? We just want to know which ones you are currently using. You may be considering something else. You may be looking at other things. I am going to launch this.
5:27
Boom! It has launched. I’m going to let it go for about two minutes here.
5:32
Wow. Look at that, those Cognos users just hitting that button. Just, bam!
5:44
While you’re doing that, I’m going to look at the question window and see if there’s anything that needs to be. And nope, nope, we’re good. Like I say, there are two people today with the last name of Powers.
5:56
That’s just amazing.
6:00
I know one of them.
6:03
I don’t know if I’m related to the other though.
6:07
Who else do I know? And here I’ll give this another minute, let you guys have some chance to answer it.
6:13
Ah, are all doing great checking that.
6:19
I am going to close the poll here in one socket, boop!
6:26
OK, I’m going to share the poll results, So hopefully you’re all seeing the poll results 78% of you are coming at us today with Cognos: 78%!
6:43
That’s a pretty high number.
6:44
That actually is going to play into something, because when it comes to data cleansing, you probably are here because you are having a little frustration with what you can and can’t do, as far as data.
7:15
Thank you. one of the things we going to deal with is And, by the way, everybody, Steve Reed Pitman is in the background helping me out with technical issues today. Hey, Steve, 48% of you with Power BI and 41% of you with Tableau, thank you very much.
7:32
And then 9% other, I’m going to hide that. Let’s get back to it.
7:38
So let’s first talk about why do we need to cleanse data?
7:43
OK, why do we need to cleanse data? Look at here. Here is, here is top Chef season 48, and we’re trying to set everything up.
7:54
It really is the same as a chef, preparing ingredients for a nice meal.
7:59
Nice meals don’t happen, somebody gets out there, somebody, slices and dices, 89% of you voted, by the way, Peter to answer your question.
8:11
So, 89% of you.
8:15
That’s a pretty gosh darn good numbers, almost 90%.
8:22
Meals don’t magically appear. I mean, I know DoorDash makes you think they do.
8:27
But even then, somebody had to get out there. Somebody had to assemble the ingredients, somebody had to put it together.
8:35
All these reports, all these dashboards, all these things, they don’t happen magically, 90% of the work has to do with preparing the underlying data, It truly does.
8:47
I don’t care which of these three tools you’re talking about, OK?
8:53
You’re never going to get good reports without good data, OK?
9:01
So, we’re going to make it magically delicious, look at that, Look at that beautiful. Now to your vegetarians out there, you probably don’t think that’s beautiful.
9:10
But to those of you who are not, that looks beautiful, OK. I should update this slide. Make it more vegetarian friendly.
9:19
But hey, data’s everywhere.
9:24
And while you can create these things quickly, and in these modern tools, you still going to have good data.
9:31
You still going to have a good, underlying, absolute requirement to make these things accurate, which means that you can then drive your business and you can make informed decisions.
9:45
So what is good data?
9:47
Accurate, up to date, informational, OK.
9:56
All right.
9:59
What’s bad data?
10:01
It’s in the wrong format.
10:02
It’s not normalized across multiple sources. We’ve got different granularity levels. It’s not analysis friendly. There’s outliers. There’s data mistakes. Look, those of you who are still dealing with old CRM systems where it has manual input because it’s really a unix application that’s been put into a Windows window.
10:22
You know what I’m talking about?
10:24
You’re going to have misspellings. You’re going to have things all over the place. You’re going to have problems. You’re going to have issues.
10:30
But then, I’m going to show you one in a few minutes that is a beautiful, beautiful, looking Excel report, but it’s absolutely not analysis friendly.
10:43
Just because something is pretty, does not mean it’s useful. I mean, I know this as a fact because I’m pretty, but I’m not useful, OK?
10:52
Then the different granularity issues, hey, my forecast is at the monthly level, but my sales are at the daily level, and my inventory is that the weekly level. How do I report on that?
11:34
Any other questions come through? Let me take a quick peek! I do not have that information, Scott. All I know is how many total, I don’t know what individual answers were.
11:44
All right, Let’s keep this going.
11:47
So Before we get into it, let’s do a quick very quick look at something. And this is one of the biggest problems that all of you have had at least once in your career.
12:02
I promise you.
12:08
And I will wager that at least half I should have had a poll for this, at least half no, are going to know what I’m talking about in this next slide.
12:20
Whether or not you’re trying to report off of a transactional or a dimensional database.
12:27
Hey, whether or not you’re dealing with normalize tables centered around specific transactions, or whether you’re dealing with a proper kimble, de normalized, with dimensions and fact tables.
12:41
This is important, in the sense, because how much you have, to do, with cleansing, can come down to what type of database you’re using.
12:56
If you are in a good dimensional database, the cleansing you’re doing is going to be slightly different than the cleansing that somebody with a transactional is going to be doing.
13:10
This is especially true if you’re in a Cognos world, where dealing with that transactional data is a bit easier because the tools are meant for it.
13:21
Whereas if you’re working with a dimensional, sometimes it’s easier to do some of that cleansing in a Tableau prep or in a Power Query editor.
13:30
It all depends.
13:44
When I’m talking about transactional versus dimensional, what are we talking about here?
13:50
A transactional database, your input system, your EW, your ODS, the things that are not really good data reporting systems.
14:05
Things that are meant for the input of data. They’re built around rules of entry there built around getting data in quickly. You know why the DMV stinks so much sometimes, because when you go in their systems are built for the input, not the output of data. So when they say, I’ve got to look up your license information, it takes three days.
14:26
They’re built for the input of data.
14:29
And the problem there is that they’re structurally, typically complex.
14:34
There are technical naming structures.
14:37
You don’t have as much flexibility, all right, and that makes it a challenge. What’s the transactional look like this?
14:48
They’re not usually built around business rules.
14:51
A lot of them are third party, those of you in education who’ve had to deal with things like banner and things like that over the years.
15:24
A lot of them are third party.
15:26
You can’t change them unless you pay somebody, $10000 a minute, to come in and change one field name and go, that’ll be $10000, please.
15:38
The other bigger issue, is that they don’t always have all the data you need.
15:43
That is a real big problem for a lot of these systems.
15:47
Think about this, And I would wager that most of you have experienced this: You need to do a report that shows your actual, versus your budget, or your forecast your actuals are stored in your EW.
16:02
But your budget and your forecast, that’s in a spreadsheet under Donna’s desk. OK, Donna has been keeping track of that for 10 years, And Donna is the only one who knows where that Excel spreadsheet is. How are you supposed to report on that?
16:28
Conversely, if we have a dimensional database, if we have a star schema, this is built for business.
16:36
This is going to have different data cleansing needs, because here, we’ve got well built fact tables. We’ve got well built domain, mentioned tables.
16:49
Here we’re usually doing things like renames, or cleansing of data, or possibly, maybe splitting out fields, different cleansing requirements a lot of times.
17:01
And I would wager. Again, I’m just a wagering mood today apparently.
17:08
I think that for a lot of you, you’re probably here today because you’re trying to figure out, maybe, do we keep the product we have?
17:17
Do we buy a new product? Hey, we’re looking at buying a new product.
17:24
What am I going to get in that product? Is it going to meet the needs that I don’t have met today?
17:32
A lot of that is really going to reflect upon your data source.
17:37
Look, we’ve got a really good dimensional source backend. We spent the last five years putting together a good kimbal data warehouse, but we need some data cleansing, OK.
17:48
Then what I’m going to show you is what tool might be best suited for that.
17:53
Hey, we’re still coming off of a transactional system, but we have to keep it, we need to do unions, and we need to do a lot of joins. And we need to do a lot of functional type stuff with manual sequel, OK, let me show you which tool is better for that.
18:12
That’s probably why you’re here today, is to figure out should we stay with the tool we have? How do we get the most out of the tool we have?
18:20
And when I see that 78% of you are with Cognos, that tells me that a lot of you may be struggling with this whole data source thing. Struggling with older data sources.
18:34
Struggling with writing manual sequel, struggling with how to get that data in, and you may be looking at a new product, maybe looking at a new tool.
18:45
And that’s OK.
18:46
So let’s start out then, by looking at what options are available to us in Power BI.
18:54
Power BI has one of the most well rounded tools because it does have a built-in solution.
19:24
Power BI has a built-in solution.
19:28
We’ve got what’s known as Power Query Editor or PQE and PQE allows us to do a lot of things with M.
19:39
M allows us to create complex advanced scripts.
19:44
I really like what we’ve got with Power BI.
19:48
I like it so much that I make it at my first demo. So let’s take a look at something.
19:55
Here is that spreadsheet I was just telling you about.
19:58
Look at this pretty spreadsheet.
20:01
Josh golly, I bet Donna spent a long time putting this together. They printed it out. They send it off to everybody every month. I mean, come on gag.
20:13
This is a beautiful spreadsheet if we’re into spreadsheets.
20:18
I’m not into spreadsheets, I mean, other things, OK?
20:23
But, if you’re into spreadsheets, this is gorgeous.
20:25
Problem is you can’t port off of this.
20:28
If I upload this into Cognos, if I upload this into Power BI, if I upload this into Tableau, this isn’t going to cut it. It’s not going to fly.
20:37
This is what we’re talking about when we say data cleansing.
20:40
OK, how do I make this report ready?
20:45
Well, let’s find out.
20:48
So here I am in power BI.
20:50
I’m going to go ahead, I’m going to get my data. I’m actually going to open that exact file.
20:58
I’m going to bring it on end.
21:00
So this is exactly what you just saw.
21:03
That’s exactly what you just saw.
21:05
And I’m going to go to transform data.
21:08
I’m going to go to transform Data, so now, I’m inside of the data cleansing tool that is built in to Power Query Editor, OK?
21:23
It’s built into it.
21:27
It automatically tried to do some things we see over here are applied steps. We’re going to get rid of those real quick.
21:34
So I’m just looking at my raw data.
21:37
All right, this is my raw data.
21:39
If we look at the source, we can see that those first two rows don’t have a value.
21:44
And it’s really in the third row where we have our headers.
21:47
So, hey, with one simple click, I can remove the top two rows.
21:54
Bye.
21:56
Look at that.
21:57
My top two rows are gone.
22:00
Know what I can do now?
22:02
I can go ahead and promote those. And I can make a column header out of that. But, before I do that, let’s get rid of some of these nulls.
22:11
Victoria, I just looked over the chat window. Victoria, Yes, this functionality, this tool has been built into Excel since before some of you were born.
22:24
You are absolutely correct. It is the same type of tool you would use in Excel. So, any of you who are power Excel users, this is a nice, easy, smooth transition. You’re already used to this tool.
22:37
You just get more, OK?
22:40
As MCR, one said: Give me more. Give me more, OK.
22:46
So, now, what I’m going to do is, hey, I’m going to get rid of blank rows.
22:51
Boom.
22:54
Wow.
22:55
One, click, one button.
22:58
Now, we’re down to 46 rows, and we see our steps over on the right, we see what we’re doing. The nice thing is that I can walk through each of these steps.
23:07
Look at that.
23:10
Our last one, removed four rows.
23:14
Let’s do something else real quick.
23:16
How many of you have struggled with writing, ETL, or writing Python scripts, or writing are scripts to fill in, things like this first column? Oh, my goodness. I don’t have time for that. I’m too lazy, Look, how about I just click Fell down.
23:36
I love one button.
23:39
I love success, one button, and now I filled down this. Ladies and gentlemen this is data cleansing.
23:48
At this point, I can go ahead and I can say, hey, I’m going to use my first row as a header.
23:54
Boom.
23:56
OK, shiny and pretty scary.
24:02
Wait, what about those extra total rows and those extra header rows I saw when I was looking at it, OK?
24:10
Does not contain.
24:12
Boom. Gone.
24:14
But what about these total rows? All right. Hold on, hold onto your hat.
24:20
Does not contain total.
24:26
Boom.
24:28
Wow.
24:29
In minutes, in mere minutes, I took this from 16 columns by 52 rows, 2 16 columns by 37 rows.
24:39
I’ve got something that’s really starting to look nice. Hey, but you had some weird things in there, You had a Spark line and you had extra totals. All right. Hold on.
24:52
Remove Columns.
24:55
Wow.
24:57
Although we’re not done yet because, gosh, golly we cannot report on our months like this. This is too difficult. If I’m in Excel, pivots are wonderful.
25:08
If I’m trying to build reports, though, we can’t do that.
25:14
Hey, click the right button dummy.
25:17
So I’m going to take these 12 columns, and I’m going to un pivot them.
25:30
Sorry. I added noise going on. I am pivoted them, look at that.
25:34
So now I can take this, make this month, I can take this, make this quantity, Now my data is ready to go.
25:45
Again, look at that.
25:46
Look at how quick I was able to take that original spreadsheet, this, and it is something like this. This is the power of a good data cleansing tool.
25:58
Before I leave this one, I do want to show you just a couple more things here. I’m going to add a column. And as I said, you could use M to create your own custom columns. Hey, how about this? I’m going to create one called begin date.
26:15
And I’m going to use an M functions.
26:16
And I’m going to say, what am I saying here. I’m going to do a date from text.
26:25
I can do a date from text, just write in a little bit of code. Nothing crazy here. I’m going to put in my month field.
26:33
I’m going to concatenate that with one paste that comma.
26:44
Ampersand I’m going to add in a year.
26:49
Might help if I put that in some quote marks, because I’m dumb today. I told y’all I got the brain fog.
26:57
And what did I forget? Somebody told me what I forgot, I forgot something.
27:02
Ampersand.
27:05
I want my comments in the wrong place, that’s better, OK, boom, look at that.
27:14
I created a brand new column, and you know what’s even better, is using that new column.
27:18
I can click one button, and I can create an end of month.
27:23
And using that one column, I can create a days in month.
27:28
How’s that?
27:29
How many hours have you spent doing ETL?
27:33
How many hours have you written scripts?
27:37
This is the importance of data cleansing, OK?
27:40
This is absolutely the importance of having a good data cleansing tool.
27:46
It takes us near minutes, but Power BI is not the only one that has something, otherwise I wouldn’t be doing this class.
27:55
The Power BI tool is a tool for folks who are used to this interface.
28:00
People who like Excel, people who’ve come to this from Excel, People who don’t mind writing code, OK.
28:08
What about other options?
28:14
Let’s look at our Tableau options.
28:20
I’ve got Tableau Prep Builder, which to some of you may look a little bit like SSIS.
28:29
It may look like other drag and drop tools you’ve seen in the past.
28:37
It may look like things that you’re used to seeing.
28:39
And, Tableau Prep has a secondary tool called Tableau Prep Conductor, which allows admins to schedule these tasks.
28:52
So we can create what are known as flows.
28:55
Hey, it’s a self-service client.
29:00
It is, it is a true ETL tool.
29:03
You create flows that are repeatable with documented steps and then we can schedule them to run on intervals.
29:10
We can schedule them to run at different times, weekly, weekday, whatever we want to do.
29:18
Why is this any better or any worse, than Power BI?
29:24
It really is the interface difference, how this one looks in comparison to what you just saw.
29:32
So, hey, let’s do a view of it. Let’s take a look at it and let’s see what’s different before I get into it.
29:40
Real quick, the licensee Tableau Prep Builder is actually part of your Tableau creator license.
29:49
The Tableau Prep Conductor, however, is part of the data management add-on so you get Tableau prep with your creator license.
29:59
You would need to do some additional work with the data management add on, OK, Andthat is, since 2018, they’ve been doing it this way. If they have changed it, well, you know, sorry.
30:14
There is more information at that URL down the bottom there, so you can get more information on the licensing.
30:31
You can see the difference between the licensing options.
30:38
What are some advantages? It’s free with creator. It’s got a graphical interface. It’s installed or it’s integrated with desktop.
30:46
We can do things in a documented, repeatable way.
30:50
We’ve got data previews.
30:58
And, again, it’s an interface that some of you may find more, may find more suited.
31:09
Let’s take a look at it.
31:12
So, here it is, I’ve got Tableau Prep open, I’ve got it ready to go, and what I’ve done is I’ve brought in to data connections. I brought in to more Excel files.
31:24
These are some best seller data at the weekly level.
31:31
And you can see that each one of these, if you look over here on the left, you can see that each one of these has multiple tabs in it.
31:42
So again, another situation where I need to cleanse my data because I can’t report out a straight out of something that’s got all these tables like this.
31:52
Not to mention the fact that I’ve got the data at a weekly level, so how am I supposed to deal with that?
31:59
But let’s look at something else here.
32:02
I’m going to go ahead and I’m going to bring over one of the tables, one.
32:09
So this is the table mass market.
32:12
It’s got a rank column. It’s got an info column. And it’s got a week’s last week, weeks on list column.
32:21
Look at that pipe right there.
32:24
You can probably already imagine that is going to give us trouble.
32:29
You can’t report on something that looks like that, Can you?
32:37
I’ve got no middle name, Lee Child, 999, and has been number, a title, and all of this is jammed together into one field.
32:51
How am I supposed to deal with that?
32:54
Same thing here. My last week, weeks on List, My Rank.
32:58
We got to work with this stuff.
33:01
First, how do I deal with all of these different tables? Well, that is easy enough.
33:08
Because here on Tableau prep and in Power BI Query Editor, I can do a union.
33:15
And I can take all of these tables. I can union them together.
33:20
I can bring them all together into one table.
33:24
Bam!
33:26
Look at that.
33:27
I’ve now got my first step in my flow, and I’m going to go ahead and I’m going to rename that step for.
33:34
It’s documented, well, I’m going to call it 2008, and I’m going to type that correctly.
33:40
That slash on there.
33:53
Now, I’m going to add more steps to my Flow. So as you can see, a very graphical interface compared to what we saw in Power Query Editor, I’m going to add what’s known as a clean step.
34:05
And my profile pain down below changes to show me what’s going on. I see a preview of my data.
34:12
I’ve got some statistics on my data.
34:15
For example, if I hover over Mass market, I’ve got 10% of my data is in mass market.
34:23
So not only am I able to clean my data with this tool, I can also get some analytics on my data.
34:34
But could I do that in Power Query Editor? Yes, you can.
34:38
You can see the same thing and they’re just in a slightly different place.
34:43
Really, so far, the big difference, this is a little more drag and drop than the other one.
34:49
That’s it.
34:51
But let’s take a look at that field I was telling you about a minute ago: the info field, Title, author, price, is Ben.
35:00
I need those in different fields, OK?
35:03
Oh look, when I click on this card, just like I saw in P slash E, I get some buttons. I get some things I can do automatically.
35:14
Let’s do an automatic split: bam, I can rename just like I did with my pivots.
35:24
And I can learn how to spell today.
35:28
Spelling, it’s not just for breakfast.
35:34
Look at that gang.
35:36
Right, one click.
35:41
My date is now ready to be reported on or at least much better. And, you know what? I don’t need this one anymore.
35:48
Bye.
35:50
I can get rid of that column completely. Just like you saw me get rid of columns in PQ by.
35:57
So, these can go to easy enough.
36:01
I can rename them, but wait.
36:05
Does my price look right? Now, it looks like my price is coming through as taxed, a big data cleansing issue that happens all the time, especially, you know, you know what I see all the time.
36:20
All the time, I see a year, come through his text.
36:24
I don’t know why, but you all seem to want to give me a year as a bar chart. Stop it.
36:31
Year is not a bar chart.
36:34
Neither is price.
36:38
It’s a number.
36:42
Oh, look at that, when I do that, it gives me a histogram.
36:45
So now I see that 1%, or that much, 4% or that much. So again, getting those analytics as well as getting my data cleansed.
36:55
I want to see my whole list.
36:58
There it is, There’s my whole list.
37:03
So there’s my list of data.
37:08
There it is, ready to go.
37:11
I can also bring in some more data.
37:16
So, here’s my data from 221.
37:18
I’m going to do the exact same thing. I’m going to bring in that mass market.
37:23
Rename it fib 21.
37:26
So, again, just dragging and dropping, I’m going to do my union again.
37:31
Boom, and now I want to union this, to this. Golly, I bet I’m going to have to write hours and hours of code. Ah, not!
37:43
Because this is a drag and drop tool.
37:46
I just drag it over.
37:48
Look at that. I just drag it right over. It says, What do you want to do, do you want a union it, or do you want to join it.
37:54
Well, I want to union it.
37:58
Oh, Now my clean step is in the wrong place, OK.
38:07
Wrong way.
38:11
How’s that?
38:13
Drag and drop simple enough.
38:16
Any skill level, no code.
38:21
There you go.
38:22
When you’re sitting down and you’re trying to think, gosh, should I use Tableau prep? Should I use Power Query Editor?
38:29
Think about the person who’s doing this.
38:31
Are they somebody who’s going to be comfortable writing code, or are they somebody who’s going to be more comfortable doing this kind of thing?
38:39
Now, with power, let me drop back to Power Query Editor real quick.
38:43
When I do this, when I’m ready to go, I close and apply, and I bring it back into Power BI Desktop. Now, I can save this off, and I could use it as an external, I can export it, and use it as an external file.
38:56
One thing I can do in Tableau Prep, which is kind of nice, is when I’m ready, I create an output step.
39:04
Now, my output step can be saved as either file, a publish data source, or a database table.
39:14
So, you don’t actually have to use this output in Tableau Desktop.
39:20
OK, you don’t.
39:24
I just got a comment in there. June said, Should Tableau should really sell prep. Builder is a personal ETL tool. I kind of agree with you on that June, because here are my output step. I could send this off to a hyper for Tableau direct. I can send it off to an Excel S, or I could send it off to a CSV.
39:44
So guess what?
39:45
I could use Tableau Prep and bring it into Power BI.
39:51
I could use it in multiple places. I can make an XLS out of this.
39:57
All right.
40:00
And, Ellen, you asked, When I do a union, can I union three or more tables at once? When I did this, I union seven tables at once?
40:09
So this is a seven table union in one shot.
40:15
Jorge, your comment about data size absolutely valid if I’m handling a million rows in the data source. Absolutely.
40:24
So here’s where I have to think about any of these tools.
40:31
If I’m trying to cleanse a million rows of data, any of the three tools we’re looking at today may struggle, may have some issues. But it also depends upon the machine I’m doing it on.
40:44
OK, am I doing this at on a desktop machine again. I’ve got 64 gig of memory on the machine I’m using. I’ve got SSD drives on the machine I’m using.
40:56
So, hey, could I handle a million euros? Yeah. I could.
41:01
Could your standard person in your organization handle a million rows? I don’t know.
41:07
They’re going to struggle a lot more than I am. And as a matter of fact, I’m thinking of upgrading this to 128 gig of memory.
41:14
So, yes, we’re talking about a desktop with 128 gig of memory.
41:20
That’s not your average person’s machine.
41:23
Is this going to be better handled through a traditional ETL tool with running on a database or running on it on a production quality server, depending on the size of your data source. Sure.
41:41
So, Matt came through and I knew your name looked familiar. Hi, Matt.
41:46
Some of these tools will allow you to write back some of them will not. Power Query Editor is non destructive.
41:54
A matter of fact, if you are using Power Query editor, you do have to do import versus direct query to do some of the things you just saw me do.
42:02
So, here, in Tableau Prep, I can actually set my output to write to a database table.
42:14
So I can set it to a database table, not necessarily to a data lake.
42:19
I’m not writing back out that way.
42:23
All right, so I can, though, add a connection. And you see here are my connections.
42:28
So I can go off to Redshift. I can go off to SQL Server, et cetera.
42:34
But I can write it out as a table.
42:38
In Power Query Editor, I’m writing it back to Power BI.
42:42
I can do it as a data source, but it’s non destructive.
42:49
And so Alan, you’re bringing up the same thing. I just said performance issues with data transformations in Power Query.
42:56
You’re going to see that with either of these tools, because remember, this is installed on a local machine, if you’re and you’re doing an import.
43:05
OK, Gang, the amount of windows I have open right now. Here’s a quick sneak peek: all look at that. OK, I’ve got a lot of windows open, But I’ve got an SSD drive with 64 gig of memory.
43:18
Alan, if you’re on a standard machine that somebody gets a typical company that’s got 16 gigs and it’s got a standard, hard drive on it.
43:28
No, it doesn’t surprise me that you’re getting problem performance issues with millions of rows.
43:34
OK, It does it because you probably have Outlook and Teams, and SharePoint, excel, and all these other things up at the same time, right?
43:46
And, Jorge, your question about the fact that you’ve got a billion rows out there.
43:52
I mean, these are the kinds of things that we absolutely can help you with.
43:56
You can call us up. We can help you do an assessment, its interests. We can walk you through it.
44:01
We can figure out one-on-one what’s the best tool, given your size, kay? Everything I’ve shown you so far today is a desktop tool.
44:12
Keep that in mind, please.
44:14
It is a desktop tool.
44:17
I’ve got about 15 minutes left, I want to keep going here.
44:22
So, again, I can have my output, my output, I would then run my flow.
44:27
OK, I’m going to do it as a file, so it doesn’t yell at me, because I don’t have a data connection.
44:31
Hey, I can set it out to a CSV.
44:34
Boom.
44:35
There it is.
44:37
I can definitely do more complex stuff. Here is a much more complex flow.
44:44
Here, I’m taking the monthly data. I’m doing a lot of data cleansing. I’m pivoting.
44:50
I’m doing multiple joins.
44:52
I’m taking it in with those unions you just saw. So the question earlier, can I do multiple tables?
44:59
Here we go on pivoting. Is much more of a challenge I can do on pivoting much more easy Doug inside of Power Query Editor that I can do it in Tableau prep.
45:12
So possible, just a little trickier. Everything’s a little trickier in this kind of interface.
45:19
I also don’t have the ability to write the M code like I do in Power Query Editor.
45:25
But I can bring in more joins. I can bring in this. And again, I can create my output file.
45:34
But before I move on to the Cognos options, you guys have asked a lot of questions about Tableau and Power Query editor, again, desktop based tools.
45:45
I’ve got almost identical features between the two of them and Doug and some of the other folks. I’ve got a handy dandy chart at the end, OK? Just like Steve with his notebook, I’ve got a handy dandy chart that’ll help us solve blues clues.
46:02
Now, that’s the greatest job in the world, isn’t it?
46:05
B and Steve, and talking to salt and pepper shakers all day.
46:09
Man, that’s the life, But, I do have a chart that will answer some of those questions.
46:16
So, let’s take a look at my last option here, The Cognos Analytics options.
46:23
What does Cognos have? that the rest of them don’t have?
46:25
Well, it’s the oldest tool on the block since 1969. Cognos has been bringing you an enterprise wide solution to handle all of your business intelligence needs.
46:37
You’re like, that was my marketing voice there.
46:40
I know, Scott Felten, that here, Scott loves my marketing invoice, gosh, golly.
46:47
But the Cognos options: they’re more focused on modeling.
46:53
So, for those of you who are coming at this with a transactional system, your modeling capabilities are going to be handled much easier.
47:06
You’re trying to turn transactional until dimensional.
47:10
I gotcha.
47:12
There are small set of cleaning features. I got whitespace Trim, I got case conversions, I got null Value, but guess what?
47:19
That’s going to be better handled inside of data modules, which seen is that 78% of you are Cognos users. You’ve been probably waiting for me to come to data modeling and data modules.
47:33
So PH asked, Is this for self-service BI or do you propagate really doing data prep in Power BI or Tableau?
47:41
So, that is a perfect segue question.
47:45
For Cognos users, you do have a little bit more self-service option through the use of data modules.
47:54
OK, and that’s where that tool is going.
47:57
Data modules allow for that broader self serve package creation for that broader, Hey, I want to upload my own files.
48:09
I don’t get as many options, but I definitely get something that is more self-service. I get something that is easily pushed out to the enterprise. I get something that is sitting on a per ducksion.
48:23
Quality server can handle some of these larger data sets better, especially framework manager.
48:32
So, if I’m using Framework Manager to do my modeling, OK, I’m using it to do my data modeling, and then I’m bringing that into data modules IPs.
48:45
This is the next generation of data modeling cognisant, data cleansing, and before anybody asks, because I know you will.
48:55
Yes, data modules is the future of modeling inside of Cognos. IBM has indicated that at some point Framework Manager will be supplanted by data modules.
49:11
It is currently, I would say probably 80%.
49:15
There are some still some things that are deal breakers like I can’t do parameter maps.
49:21
No, just little things like that aren’t there that are not in data modules that are still in FM.
49:27
Now I’m talking to that 78% of you.
49:30
Those of you who are FM people, those of you who are DM people, DM is 80% there.
49:38
But the nice part like PH just asked, can this be self-service BI? Yeah.
49:44
Data modules can be, it can be enterprise wide. It can be spread out. It can be delegated, It can be given to multiple people.
49:55
And I can do these kinds of things. Here, are the clean steps inside of data modules.
50:00
Oh, look.
50:02
I can trim leading and trailing whitespace.
50:05
I can do case conversion’s, sub strings.
50:09
Can I do this stuff in FM? Yes.
50:14
Here I am an FM.
50:15
I’m going to create a new project.
50:21
I’m going to connect up to a SQL Server for this, it’s an Azure SQL Server.
50:27
I’m going to do it in English just because, because I don’t know, I do have some Canadian folks, we could do it in French today, but sure.
50:35
Comet Oliver.
50:39
I’m going to bring in my great outdoor sales.
50:46
But notice that I’m doing more along the lines of database centric stuff.
50:53
OK, I’m bringing in tables here.
50:56
I’m going to bring in product and product line and product type.
51:02
And I’m going to bring in my time dimension in my order header, my order method, my order details, and for right now, that’s all I’m going to go ahead and bring in, know it for right now. I’m going to let it create the joins, just because.
51:20
Here, I’m doing something that is more traditional, data modeling things. You’re looking for things, but that doesn’t mean that’s all I can do in here.
51:31
Absolutely, I can do more. Because I can do things like I can create a new namespace.
51:41
And I can create second namespace called hmm.
51:49
It was the wrong thing.
51:50
I can create another namespace.
51:53
I can call that one, may develop review. And know where I’m watching the clock, for those of you who are worried.
52:02
So here I can take this, and I can flatten out my tables.
52:08
So I’ve got my three product tables.
52:11
I can merge those into a new query subject, OK, and I can take those, put it out here in my Developer view.
52:24
And I can rename this, my product dimension. So notice that I’m doing things that are much more modeling centric.
52:32
I can come back here. I’m going to take my order details, meritor header.
52:37
I’m going to merge those into a new query subject.
52:40
Yeah.
52:41
Take that down here into my Developer view.
52:46
And I’m going to call that Order fact.
52:50
All right.
52:50
I’m going to take my order method. I’m going to create a shortcut on it, Push this down here.
52:57
OK, I’m going to create a join between those two.
53:06
And now I’m starting to get my star schema.
53:09
So when I’m talking about data cleansing in this respect, I’m talking more about building something.
53:21
That is around getting me from that transactional to that dimensional world. And then, again, obviously, before I push this out, I’m going to clean this up, right. I can remove columns.
53:34
OK, I can delete this stuff.
53:41
Come on, where’s my delete? That can go away. This can go away, there was a question just asked, if this is available, there will be a recording as well as materials on the Senturus website when we’re done with this. So, look, I’m still cleaning house. I’m still renaming columns, OK, you know, this becomes product type.
54:02
A little bit more manual work though, and I can create my own fields.
54:08
I can create my own filters.
54:11
OK, so if I wanted to create, let’s take this one right here.
54:15
Let’s go into my order fact, and say hey, I need a new column.
54:21
Great, no problem.
54:26
I can take my quantity times my unit cost. Look at that.
54:34
Now I’ve got revenue.
54:37
Still cleansing data, still doing things, but hey, can I pivot in here?
54:41
Now, can I pivot in, here?
54:43
No, that’s not what this is for.
54:46
This is a data modeling tool that gives me the ability to use functions and gives me the ability to do things, to create new columns to do code. Again, a very different interface than we just saw.
55:00
Very different interface than that Tableau prep one.
55:03
Validate that I do OK.
55:06
And then I publish this out as a package, OK, once I have a package.
55:16
Now, I can work in data modules.
55:20
Now, with data modules, I can do some fun stuff.
55:25
Matter of fact, I’m going to show you here are three uploaded files.
55:29
Now, I’m going to bring in those three uploaded files And I can take these three and I can create a new table from them.
55:39
And look at my options for tables. I could create a view, I could create a union.
55:44
I can make a copy, I can create a join. But notice that four of them are graded out.
55:49
Why? Because I picked three tables.
55:53
three tables, these are my only two options, OK, so I’m going to union that.
55:59
I’m going to call it all regions.
56:04
And there’s my union data.
56:13
This, going back to our ace question, this is meant for Sarah. I believe it was Jorge’s, question, nos, PHS Quest, or excuse me. This is more Men for self-service.
56:24
OK, this is the kind of thing that I can give off to anybody in my organization. I can create calculations. I can create relationships. I can create filters. I can cleanse individual fields as needed.
56:39
Not all, I can split all those things you just saw in Power Query Editor, all those things you saw in Tableau, Prep.
56:48
I can do splits.
56:50
I can cut. I can rename.
56:52
I’ve got the same functionality. I can preview my data.
56:57
I can see what’s going on here.
57:00
I can create my own data groups.
57:02
I can hide it. I can remove it. I can format it.
57:07
Different interface, not a locally installed tool.
57:12
I am connected up to our Senturus web page. I’m connect up to our Senturus Cognos site.
57:19
OK, Ellen?
57:23
Yes, I just, union three tables, I, union to all three of these Excel files, which are acting as tables.
57:31
So, yes, absolutely. Oh, I’m sorry, that was a scroll up. I was going to say, did I answer that? I will answer one question.
57:41
Too sharp question came through: which tool out of the three is the presenter’s favorite? Do you think I’m really going to answer that?
57:51
We teach, sell, and train, and work with all those.
57:57
No, cannot pivoted on pivot the same way in here that I could. When it comes to that, honestly, Power BI is the clear winner. Power Query Editor is the clear winner.
58:07
I can pivot in Tableau Prep on Pivot as much more challenging, pivot, and on pivot. Power Query Editor is the clear winner, and you know what to shard. That is the whole reason why we’re here today.
58:19
Which of these is going to be best for you?
58:21
Well, what do you already have?
58:23
What are you looking to move to?
58:25
What is the skill set of the people that are going to be using it?
58:29
Because I hope that you’ve all seen. I’ve got two minutes left. I hope you’ve all seen. Each one of these has a very different interface. Each one of these has a very different look and feel.
58:40
Here are the, here’s the handy dandy chart.
58:45
I am going to put the handy dandy chart in the chat window for you all.
58:49
So you can take this home with you. It’s your parting gift for me to you.
58:55
Some of these things are much easier to do.
58:59
Indifferent tools, OK?
59:02
That’s the real answer there to shard, which one do you already have? Which one do you want to spend licensing on?
59:09
Which one who’s going to be doing it? How are they going to be doing it?
59:14
Refer to this chart.
59:16
Take a look.
59:17
Look, knowing which options are available is going to help you decide.
59:21
Once you’ve got a tool selected, you can get in here and cleanse things.
59:26
And that’ll lead you to analysis of your data.
59:29
We have more cross vendor comparisons available on our website. We’ve got one for dashboarding, 1, 4, 5 areas where Power BI options to Tableau.
59:41
We’ve got one for Snowflake versus Azure, OK. So we’ve got more of these on The Senturus website. Please log in and Take a look.
59:50
Senturus provides hundreds of free resources.
59:54
Hey, go to our website. We’re committed to sharing our BI expertise. We’ve been sharing it for over a decade.
1:00:02
It is three o’clock. Let me get you all out of here. We’ve got two upcoming events. We’ve got an Agile Analytics for Cloud Cost Management. We’ve got that’s on November third, so that’s right around the corner. We’ve got KPIs for multiple sources in minutes. That’s also in November, November 17th. I’m going to be doing another one in December, for those of you who like listening to me. I don’t know what I’m doing in December yet.
1:00:26
Send us some suggestions. We’d love to hear your suggestions.
1:00:32
Little background about us, we concentrate on BI modernization. We provide a full spectrum of services. We have perfectly round heads, as you can see here on this graphic. We really shine in hybrid environments. If you’ve got all three, we’re here to help you.
1:00:46
We’ve been doing this for our 21 years, 1300 clients, 3000 projects. All my goodness gracious, we’re awesome.
1:00:55
If you’re looking to join us, if you want to be part of this fun, exciting team, we’re looking for a Microsoft BI consultant.
1:01:01
We’re looking for a FinOps consultant that will be part of our new cloud cost management product in practice. Send your resume to jobs@Senturus.com.
1:01:11
See the job descriptions at Senturus as well.
1:01:14
If you have any questions, put them in the question pane.
1:01:17
We’re happy to answer them.
1:01:19
Contact us at info@Senturus.com. I’m a minute and a half over, hey, thank you all for attending.
1:01:26
If you look in the chat window, we want to talk more than you can go ahead and you can do a follow up.
1:01:33
Thank you all for attending. Thank you for giving me an hour of your life, Toronto in the House, thank you Toronto for common. Everybody who’s here, everybody with the last day of Powers, thank you for showing up today.
1:01:48
We’ll have the next family reunion in Toronto just because get the heck out of here.
1:01:57
You’ve been beautiful, I appreciate your time.
1:02:01
Thank you all very much.
1:02:03
I will be here for a few more minutes, answering questions, You’re all great.
1:02:08
Get the heck out of here, hit the road, go enjoy the rest of your day, hundred and 65 of you came today. Thank you all very much.
1:02:18
Again, if there’s any questions, I’m here. I’m not going anywhere. It’s only 302.
1:02:22
Where do I got to go?
1:02:26
Please, feel free. Thank you all for your kudos. I love it. It’s great. You’re all wonderful.
Read more
