Data Modeling Comparison: Power BI, Tableau & Cognos

×

Michael Weinhauer
Director
Senturus, Inc.

In addition to heading up the training at Senturus, Michael’s team is responsible for the development of the Senturus Analytics Connector, which lets Tableau and Power BI use Cognos as a data source. He has been designing, delivering and selling analytics solutions for over 20 years. Before Senturus, Michael held positions at Oracle, IBM and SAP, acquiring a wealth of hands-on, practical BI and big data experience.

Read more

×

GENERAL

Q: Will this webinar recording be shared later?
A: Yes, view the recording now.

Q: Does this only compare the client side or also the server side of analytics?
A: This webinar and associated demos are based on dashboarding or end user reporting. Our detailed matrix includes enterprise/server functionality (full BI stack).

Q: How are maps integrated in each tool? Are they using a google maps interface or does each tool have built-in maps functionality?
A: Power BI has out-of-the-box functionality with Bing maps. You can get Google Maps working, but it’s not straightforward. Get more info.

Q: Do you have information about the costs of Power BI on Azure vs. Tableau on Azure?
A: Costs are unique to the tool, user counts and other engagements. For example, Microsoft is rolling in some Power BI functionality with enterprise licenses. We are a partner with all three vendors and would be happy to help quote what your organization might need across the platforms. Please contact us at info@senturus.com.

Q: What is core data services?
A: Core data services are part of the SAP framework. It allows you to embed metadata information in the data layer to be used by the semantic layer. Learn more.

Return to top

COGNOS

Q: Are data modules in Cognos a replacement for using Transformer? What is the conversion from Transformer to data modules?
A: Cognos data modules are not a replacement for Cognos Transformer. Data modules can be compared to Cognos Framework Manager. It is a lighter web-based modeling tool targeted for data analysts. Transformer is a full-fledged MOLAP modeling and creation tool.

Q: How do we migrate Cognos data modules?
A: You migrate using the same migration method as reports, with a deployment package.

Q: Are there any companies using data modules currently?
A: We know companies that use data modules. Many are targeting data analysts in functional groups who want to augment data from existing corporate Framework packages.

Q: Is Cognos looking to expose the package as one of the data sources?
A: A package can already be used as a source for a data module.

Q: Can packages be created out of Framework Manager?
A: Yes, that is the main function of Framework Manager, to model the back-end data source tables then publish a package for data use.

Q: Are there server-side tools for integrating a Cognos package with Power BI or Tableau data sources?
A: Senturus has an Analytics Connector between Cognos Framework Manager packages and Tableau and Power BI.

Q: Does Cognos data modules have additional memory/CPU requirements on the servers to create the data democratization type workflow in Power BI and Tableau?
A: It is not currently possible, read about the alternatives.

Q: How do the transformation capabilities in Cognos data modules compare to those in Power BI Desktop dataset?
A: Power BI is incredibly flexible in terms of transformations. You can change datatypes and do all sorts of changes, split, concatenate, etc. You do this with a language called M and a GUI to do it, but it’s all under the covers.

Return to top

POWER BI

Q: Can Power BI use data sources which are not necessarily MS SQL Server or flat files? Such as graph data or long strings available as JSON files?
A: Power BI has a ton of connectors, JSON is one of them. The great part about the connector experience is that, if no one has built a connector that works with your particular proprietary technology or format, you can create your own! Senturus, for example, has created an Analytics Connector to allow Power BI to connect to Cognos.

Q: Cognos gives end users the ability to create their own reports. Does Power BI also allow similar flexibility?
A: With Power BI this is definitely possible with two options. You can either download and install Power BI Desktop on your computer to create your own reports against centrally-managed datasets. The other option is to create an app workspace in the Power BI service and create reports against those datasets within the browser.

Q: Can we connect Power BI on the top of core data services (CDS) view?
A: No, it is not possible to directly import data from CDS view using the built-in SAP HANA connector. However, CDS views can be easily exposed as OData Service (API) which can then be used to fetch data from SAP S/4HANA cloud. You can use the OData feed connector in Power BI Desktop to connect to import data from CDS view.

Q: Can I run analytics in a linear regression, such as a sales as a function of other attributes in Power BI?
A: You can absolutely create measures in Power BI that are functions of other things, then display them on a scatterplot and display a correlation.

Q: Does Power BI real time have similar functions as Cognos real time?
A: Power BI refers to this as DirectQuery. You can connect to a wide variety of data sources directly and live query those data sources while interacting with the visuals. See a list of Microsoft DirectQuery data sources. If the data source is on-premise and you’ve published to Power BI service, the Power BI gateway is required and supports a similar set of DirectQuery data sources.

Q: Is machine learning or AI included with or a module for any of the three platforms demo’d?
A: Power BI has AI functionality branded as what is called Q&A. It’s still a maturing product, though.

Q: With Power BI, how do I join two tables with more than one data element?
A: You normally need to create a calculated column of the two columns in each table and then join the two tables on those calculated columns. Power BI doesn’t currently support multi-column joins.

Return to top

TABLEAU

Q: Is Tableau Desktop required or can content be created exclusively in a browser?
A: Web-based authoring can do some of the things that Tableau Desktop can do. Tableau provides a comparison reference and a feature breakdown viz. The good thing is that licensing for web authoring (Creator or Explorer) includes the same features on the associated desktop application.

Q: Can tools connect to live real-time streaming in Tableau?
A: The nice thing about live data is that it doesn’t need to be extracted to Tableau first to be used. However, while this live connection from Tableau to a database is possible, the tables themselves are not necessarily live, so the data is not updated in real time. They are refreshed on a scheduled. Tableau explains how to embed a Tableau view inside a web-application using Javascript API to refresh it whenever you need it updated.

Q: Does Tableau have dependencies on any resources external to the on-premises installation?
A: In general, no. Tableau Desktop is installed locally and can be used with local data sources without any network connection. Tableau Server can be installed on a Windows or Linux machine which could be offline or only on a local network (no internet). Tableau Online is the vendor hosted service that would require an internet connection to use.

Q: What capabilities are available on the Tableau mobile view?
A: Mobile view can be used to view any views published on Tableau Server (or tableau online) and  provides the same interactive functionality as the desktop browser views (filters, click actions, tooltips), but displays are not always rendered in formats that are easy to work with. IT’s best practice to create different views that are optimized for each platform.  Desktop browser has a lot more realestate so more information can be displayed on a page.  Mobile views are better for compact metrics and charts.  Tableau has handy templates in the dashboard developer pane which allows a window to what would be visible on a Mobile or Tablet view based on what’s currently included in a dashboard.

Return to top

Read more

×

0:07
Greetings everyone and welcome to the latest installment, the first installment for a new decade 2020 of this Senturus Knowledge Series. Today I’ll be presenting the data modeling comparison between Tableau, Power BI and Cognos.

0:23
We call this the trifecta chronicles as we tackle the interesting challenges and differences and similarities between three of the big powerhouse BI platforms that we see in the place before we get into the heart of the presentation.

0:46
We have a couple of housekeeping items. So feel free to use the GoToWebinar control panel to submit your questions. Everyone’s microphones are muted. I will warn you in advance that there’s a lot of content that we’re covering here today. And so the likelihood that we’ll get to the questions live is somewhat slim.

1:08
So get your questions into the control panel, we will answer those and post them along with the deck and a recording of the webinar in the upcoming days. So go ahead and do that. Please make sure you ask your questions with sufficient specificity and clarity that we’re able to answer them put them in the right context. Let’s see. So the first question we always get from people and throughout is can I get a copy of today’s presentation and the answer is absolutely it will be available on our website.

1:41
Its senturus.com simply go to the resources tab and then look at the resources library, or you can look at the link that was just posted to the GoToWebinar control panel. Be sure to bookmark that Library as it has tons of valuable content at addressing a wide variety of business analytics topics. You can also give us feedback via the question log on our shiny new template here that we’re running our agenda today after some brief introductions definition of what is modeling and transformation will jump right into what it’s going to be mostly live

2:15
demonstrations today and then I’ll do a little bit of comparison summary and review will give you a quick overview of Senturus. For those of you who may not be familiar with us discuss some great additional resources upcoming events, and then again time permitting we will jump into Q&A. My name is Mike Weinhauer, I am your host and am presenting the content here today. I’m the director at Senturus. I’ve been delivering selling and designing analytics

2:44
for well over 20 years at this point, and I’m excited to be here presenting to you today.

2:53
So we always like to get a little finger on the pulse of what our attendees are using in their environments. And today we’re going to ask a fairly simple question. You can check all that apply what BI platforms are you currently using? So Tableau Power BI Cognos and or others, so go ahead and get your votes in there. I let this run for a 30 to 60 seconds depending on how fast you are on your mouse.

3:24
Guys are quick for a big crowd.

3:26
Alright, we’re up to about 75% All right. I’m going to close this out and share the results. So half of you are using Tableau about 40% Power BI and a full 60%, almost two-thirds using Cognos and 21% using other if you have any comments or anything you want to do to provide a sort of flesh that out the other you can always put that in the question log to or always interested in seeing that stuff.

4:00
All right. So thank you for getting interactive with us. So let’s get into the content here. So the stages of designing a BI application can be broken down roughly as Illustrated in this slide here from sourcing the data to modeling and transforming that data to creating the content visualizing the analyzing and then pushing that out deploying that to the organization and then overarching all of those are the areas of governance security

4:30
and administration and as highlighted in red today, we’re going to focus on the source and the mostly on the modeling and transformation component of those stages. Those stages are really the same regardless of what of the technology or toolset that gets employed. So first of all, what is modeling and transformation in a nutshell, it’s creating an analysis friendly datasets or even more simply virtual stars.

4:59
All right, you have your measures and the supporting dimensions that provide the context of those right? So you have your revenue, your profit. Those are all the numbers that you’re going to roll up an aggregate. And then you have things like your products and your customers or your vendors and your time dimension. And those are the things that you slice and dice with and create your hierarchies and do all that sort of stuff.

5:20
So regardless of what the data is that’s coming in you’re going to join and clean and otherwise shape that data you oftentimes have a data warehouse that serves as maybe a core source and then increasingly a myriad other sources that people are seeking to bring in for the purpose of doing these various analyses. So the tools have evolved and have capabilities that allow you to prepare and model the data in such a way that it can be consumed and reported and analyzed the roles are shifting

5:55
towards more self-service as you can see and will you that in the tools and indeed that shift to self-service is largely. What is driven the market for tools like Tableau and Power BI and it’s forced vendors like Cognos to kind of move in that direction and that role again was typically performed by a data model or data architect. Although increasingly. It’s done by business analysts who want to combine those sources. So I put together a matrix of basic modeling and transformation capabilities.

6:29
I have a few other of these and this is the only thing you should really take from this and you can study it from the deck later. I’m not going to go through this is that there’s a whole host of things you can do around basic modeling and transformation and all the tools basically check those boxes with the exception of a few items. And so I’m going to leave that there for you. Can you can pick that up? I’m going to jump over and start in on the demonstrations here. So I’m not going to do a full demonstration of this one.

6:59
I’m going to start it with Cognos, I guess I’m just going to go in alphabetical order but the Cognos environment the back in the 2000s when this came out. This is Framework Manager. And this is one of the you know, original modeling tools that is a full client tool that was used by data Architects to design Enterprise models, and it was designed to handle multiple sources and have multiple different layers

7:30
of nesting to take enterprise data warehouse and other sources and model those into those nice virtual stars and then organize them into such a way that you could deploy them out to the consumption tools. And in this case the various environments in Cognos that you can create content in whether its dashboards are reports and what not. And so you can if you can connect to various data sources import those in definitions

8:00
and then abstract those out into that nice virtual star schema here, which I have in my business view. And then what you would do is you would publish what was called what is called a package and you can see that the package in Framework Manager aligns with the model the enterprise model that I’ve built that’s very complex and covers a number of different subject areas and I simply parse out the piece that I want to expose via these checkboxes, right?

8:28
So this is kind of a basic sales analysis that I’m going to create today in several of these different tools and then I would publish this out and then go to the web and test it out. Then I would iterate back in the model republish it and kind of that was the process for doing this. And again, this was the domain of your data architects and a handful of people that would use this client tool. It’s very sophisticated can handle multiple sources complex models complex scenarios.

8:59
You can write your SQL you can parameterize it you can secure things. There’s just about everything that you can do and this is a tool that’s 20 years old. So if you forgive the UI, that’s so dated that it’s actually probably kind of hip again. It is still very much in use in Cognos environments, but it is it is sort of the represents the old paradigm of centrally managed and pushing that out. It’s also worth noting that everything you create is largely a view on that

9:30
data, and that you could also do you can also model dimensionally in this tool? So you’re able to create hierarchies and things of that nature. Okay, the data source that I’m using here today. I’ve decided to use the Great Outdoors sales database which is a lot or transactional schema. So it represents. I think a nice data set across these different tools that forces you to do some modeling and transformation.

9:56
So you have a kind of a consistent data set and set actions that have to take place across the tools. So that is framework manager.

10:05
Now, the direction for Cognos has been more in the direction of doing web modeling via what they call data modules and so everything now happens in the browser and I can do things like if I wanted to bring in a spreadsheet if I just hover over the browser here, you can see that it lets me pull in things like flat files to create explorations dashboards or even create a data module now I’m not going to do that. I’m going to use the source that I just described here and I’m going to simply choose new data module and this is all happening from within Chrome and my sources for the module can be one of the packages. I just described from Framework Manager can be other data modules datasets or some of those uploaded files. And so I’m going to pick the database. This is a SQL Server relational database. They have some nice sort of intelligence built in here.

10:58
So if I actually wanted to go in and A sales unit sale price, for example I can go and it will use some algorithms to go and figure out where that measure might reside and any related tables and then it’ll do some of the modeling for me. I’m not going to do that because there’s some specific tables that I want to pull in here. So I’m going to cancel out of that, but you can see that Ai and machine learning and things like that are becoming more and more prevalent in these environments and those will only get more sophisticated over time.

11:30
Now what I’m going to do is again remember this is an OLAP type schema. So it’s highly normalized things are Snowflake. So I’m going to grab some branch information. I’m going to grab order information my order method.

11:43
I have three different tables that my products reside in and I’m going to grab my time dimension and when I say, okay it’s going to pull those tables in and it’s going to use the primary foreign key relationships that exist and it’s going to They could really do a much better job of sort of drawing the schema out here, but it’s going to sort of put these things together and create the relationships based on that and then from there I can go and start my modeling process.

12:13
So one of the things I have to do is that’s commonly done is create joins between the various tables that are brought in so I have to create a manual relationship here because there is no primary foreign key relationship between my time dimension and my order table for example.

12:43
All right, so I’m going to pick my order date and my day date and you’ll notice that I can define the type of joins in here.

12:49
And this is fairly common across the tools right inner joins left outer joins full letter joins to find the cardinality and Cognos has some interesting optimization capabilities in here and I can go match the columns check the date the data that flows back in a little bit of preview and then say, okay and then what I might want to do here is since that product information is In three tables, it’s not super easy to sort of look at these as a product view. So fortunately what I can do is I can click these tables and choose create a new table and you’ll notice Cognos gives me a bunch of different options here where I can create a view of those tables and what I can do is pull in only the relevant information that I want here. So this database is multilingual so it has columns for things in different languages

13:43
has and I don’t want any of that multilingual stuff. I just want to bring in the English stuff for purposes of the demonstration to keep it clean so I can very easily go in here and uncheck the stuff that I don’t want.

14:00
And pulling only the information that I do want and then I’ll get this nice little view over here that I can rename two products.

14:14
And then interestingly what I have to do here is I have to go in and I have to rejoin that to my order table.

14:28
Which I didn’t have to do in Framework Manager interestingly. If I created a view you would sort of think it would look for that underneath but in data modules you seem to have to do that. And then what I’m going to do is I can go and select these tables the original tables and I’ll say hide from the users.

14:43
So again, what I’m starting to do here is create more of a star schema that has my order method my time my products and my branch now once I’ve joined that information and you’ll notice that and Just starting to shape up here on my products view. I have I can look at the individual columns here and the product is a pretty good job of sort of automatically typing some of this stuff and you can see the icons that they use to do that and then I can go into my order details which has my measure information and I can for example control click these and you have right click and ellipses.

15:27
Personality where I can go and change properties for the data, right so I can go and change this to a currency and I can say the default and change the number of decimal places to 2.

15:40
Right and I can define things through the properties like the aggregations and the role that it represents. Is it a geographic location or time? So there’s a bunch of things that I can do to the data here now in the Cardinals environment, there isn’t a ton of there isn’t a ton of transformation type capabilities that you can do here, but there is a lot of nice modeling capabilities.

16:09
So for example, I can easily create calculations. So addition subtraction all the sorts of things you’d expect. There’s several of those and I can join in other sources and I can do things like Define a hierarchy. So I’m going to quickly do a rename on these guys.

16:32
To just product type and product line and then I’ll grab these guys. I’m just going to click them and I’m going to create what Cognos calls a navigation path. I guess they’re trying to be unique right? So I have a navigation path here. So there’s a whole bunch of stuff I can do in here and then as I’m iterating on this I can go over to try it which gives me a quasi-report development environment that where I can’t save the information or anything and I can go out here and I can try this out.

17:08
And make sure that my data is coming back and that things are sort of looking correct. So I’ll go out here and I’ve added a crosstab and I can see my product information and I’m just going to throw over my newly created product line hierarchy and then from my joined up time Dimension. I’m going to grab my current year and throw this up top. And this is what I’m going to do with all of them. I’m going to go and create a basic crosstab just to show you that the numbers if I’ve done.

17:37
Everything correctly will work out. Okay. Alright, so this is looking pretty good. So I have my golf equipment and maybe I want to drill down on that to my product type and I’ll take a look at my Putters in 2010.

17:56
Right and you can see that the numbers are sort of coming back here. Now. I’m going to jump ahead. I’m going to do a little bit of Martha Stewart on you here and I’ll go out to you know, my kind of completed module which has a nice looking star schema. I’ve combined orders in order headers into one and I’m off to the races and then I can save this out and then it can be consumed by my report authors and what not.

18:26
So there is other functionality in here that I can do like there’s data grouping and Finning I can folder and organize this so I can create folders to make this a little easier to navigate.

18:41
I can apply security for relational sources and I can even create what are called data sets which will pull the data out of that source and give you kind of a high-performance, you know columnar store in the vein of a Tableau extract or a Power BI import Alright, so that is the Cognos environment. So now I’m going to jump over to Tableau and give you a look at what that looks like. So in Tableau I have again the ability to connect to multiple different sources. There’s some probably a hundred different sources that I can connect to.

19:23
And I’m going to point this to the same data source here, right this this relational SQL Server oil TP data source, and I’m brought to Tableaus data pain and I just select the tables that I want again. It’s going to detect primary foreign key relationships and do the joints for me automatically, or I can do it myself. But in this view Tableau wants to wants to generally create kind of a singular tabular view of the data and what you’ll notice.

19:52
Is that Tableau desktop is a client tool that has kind of combined all of the aspects of sourcing content creation deployment Etc. application security into a single desktop environment.

20:07
You’ll notice that in Power BI and that’s a little different from Cognos where framework managers are a client tool for just for metadata all the content Creations on the web or data modules where everything happens on the web and there’s some overlap here between for example all the data source pane where I bring in my data source or sources and the and the actual sheets here where I create my content and so a table does is it automatically takes anything that’s numeric net of it actually has Intelligence where it picked up codes or not necessarily something you want to Aggregate and it buckets those between dimensions and measures and in order to organize this I have to sort of Which from my tap default table view into a folder view, so I’m going to create a folder that I’m going to call orders. And now I’m sort of able to you know, organize the information as I desire. And when I edit it here that does become part of the metadata and then I’m going to go in and add branch and you’ll see it’ll add Branch here in the right place and I’m going to go here and I have to kind of toggle back and forth between these.

21:22
Between these two tabs to get my Branch information and what not and I have to kind of iterate through this to organize this the right way, so I’m bringing my order method.

21:36
Toggle over here go back through this.

21:49
All right, and then I’m not going to go through all this because I’m actually going to you know, go Martha Stewart on you and plot the Finish turkey in a second. So you don’t have to watch me hide all these columns and everything, but you can see what I do. I want to just walk through this for a few home so you can see what we end up doing and you can see we’re starting to create this nice little, you know star schema and I can do things like add a calculated field. It’s pretty easy to do here much like the other tools. We’ve got an array of calculations.

22:19
Have some nice type-ahead functionality where I can pull in my column names. I only have to type a few of the items.

22:35
Right, and then the tool tells me if the calculation is valid or not. So there’s a bunch of things I can do in here and then I’ll pull in the time Dimension before I show you some sort of show you the finished product to show you that when it doesn’t know how to join. It’s going to ask you right. It’s going to say oh, yeah, you know you need to join order date from your order header table to my current or sorry to my day date.

23:00
And then it’ll join it up and add it to the schema. Okay, so I want to make sure I start cover all this stuff and then I can do a lot of the same things that I did with the other items, you know in the Cognos environment. I can just I can change the default formatting for this. So if I right click this I’ll get default properties. I can change this to currency much like I did with the Cognos data module.

23:27
I can change the default aggregations and Viewers for this right? So if I wanted unit cost to be an average instead and this is all again going into that metadata so that I can go create my visualizations. And so I’m going to in the interest of time. I’m going to jump over to my finished workbook course. It’s the last one over here and you can see when I’m finished with it.

23:52
I have my full OLTP type looking schema over here that has been neatly formatted into my measures and dimensions and organized very nicely and then I can just go out here and I also created a hierarchy by simply dragging and dropping the fields and I just go out and I create my content so I can create a visualization similar to the one that I created in the data module.

24:37
And I’m just going to create a really basic one here. So you can see when you create the hierarchies. It’s really easy to go in and drill up and drill down in those things so I can see that my Putters in 2010 might overall sales were a hundred thousand two hundred ninety eight dollars and forty four cents. Okay. So that is the sort of you know basic Tableau environment. Now what you do with what you can do with this model subsequently is publish this to the server, too.

25:07
Tableau online or to an on premise Tableau server where that data source that you created. This metadata can be reused. So Tableau desktop users can point to the server data source web users can create content or consume content that you create here. And at the same time you have some of the governance capabilities to security capabilities on the web where you can certify a data source. So there’s some governance capabilities that are built-in.

25:35
They’re worth noting that and I didn’t show you all the functionality here. I’ve tried to cover some of that at more in the in the Matrix, but you can parameterize things you can apply filters and there’s several other things you can do here, but I wanted to give you a basic feel for what it’s like to model against the source like this in Tableau desktop. All right, so now I’m going to Pivot over to Power BI desktop and so Power BI desktop very much very similar to tab.

26:07
And that you can do just about everything here in the desktop environment. You have a modeling tab a data view Tab in a visualization tab in this tool and you start out by clicking get data just like sourcing data in a data module or like you would in any other tool and you’ve got our array of connections to files database sources course Microsoft Power Platform and Azure online services, which means things like Dynamics or Salesforce or Google.

26:37
Lynx, and then more esoteric sources like Python scripts are our scripts or even custom connectors such as the Senturus analytics connector that allows you to connect to Cognos packages data modules and reports from your Power BI and or Tableau environment. So just like in Tableau and the other ones.

26:59
I’m going to enter some information about where my server is and the information I have the ability to do direct queries or and pull it into an import and I’m neglected to cover that in the in the Tableau desktop. I meant to say the default behavior in the Tableau desktop was a live connection, but I can however create extracts which are very similar to a data set or an import here in Power BI import will pull this into a sequel SQL Server tabular model.

27:28
So think of it kind of like a like an extract or a data set and that it’s kind of an in-memory high-performance structure you can write SQL and do various things manually if you want to likewise in Tableau and in data modules, you can do that as well. I’m going to stick with the kind of standard one where it’s going to bring up the navigation pane where it’s again going to show me the schemas now, it’s a little different in Power BI because they’ve done some kind of cool things in here that I’ll that I’ll show you.

28:00
So I’m just going to select the sort of base level tables here for order method and product now remember this is Third normal form transactional sort of schema, but I’m only going to pick the branch the order details the order method product and my time Dimension and you’ll see that it goes out in a queries the tables I can preview them and whatnot. I can either load these into the model but I obviously want to do some work on these and clean these up for my user. So I’m going to click the transform data button and what this is going to do.

28:33
Is it brings up the – tool which in Microsoft land is the power query editor and this is a very powerful tool in terms of having a lot of transformation capabilities and Microsoft’s done a nice job of sort of letting you just click a button in my great or in sort of navigate back and forth between these tools. Now that said they are two separate tools and you can access power query from things like Excel for example, and it’s sort of there. They’re ubiquitous transformation tool.

29:07
And when you brought in here the he’s here the tables that I brought in and you can see what it does is it applies steps to the from a sort of in a linear fashion from when you sourced it to navigation, you’ll see the steps show up as I walk through the as I walk through and make some Transformations here and you can see that I have a whole bunch of different capabilities here to remove columns remove rows split things set.

29:35
My data types all the basic things you to expect what I also have are a whole host of Transformations, like pivoting a nun pivoting and filling up and down a lot of great things that I can do here in my data set. All right, so let me show you how we’re going to do this. We are first going to I’m going to work kind of from the bottom up here and I’m going to get rid of a bunch of the extra time columns here. So again, this is a multilingual database. So the most basic step I can sort have go through all of this and select these guys.

30:07
Say, you know, I don’t want these even in my query. So I’ve trimmed that thing down and I’m going to rename it to time and you can see it added this step here and I can walk back through these steps. I can change the settings. I can reorder them or delete them if I screw them up and what it’s writing here is what’s called m-code as in man. I’m glad I don’t have to type this stuff out manually.

30:29
No, it’s not really what it stands for but it is sort of writing this out and you can go in and type this in or you can copy paste it but it’s a sort of SQL like language that’s used by power query. And so every step I right or I do here in the tool is writing some form of what’s called m-code. And now I’m going to go to my product table and this is where it gets a little more interesting because I have all my product attributes my columns my product table and then I have all these different table references and these represent all the tables that are sort of related to that product table in that.

31:07
Schema, so it’s gone ahead and done a lot of that work for me for purposes of this demonstration. Remember what I’m trying to do is create a product Dimension.

31:15
And so I’m going to get rid of all those other table references except for product type which is the next level in my hierarchy kind of a subcategory and what it they have in here is a nice expand functionality which shows me all the columns that are in that product type table and I don’t want anything but the product type information and the product type In English plus I want to pull in the product line information to give me the top level of my hierarchy. I’m going to click that and nipped Ali think that’s a word it’s going to just pull in those two columns. And then I have the additional expand capability for the product line, and I’m going to go ahead and I’m just going to pick my product line information and just like that.

32:01
I have a really nice looking little product table without having to go through and do and pull all those specific tables in and work with them sort of individually. Okay, and then on the timetable, for example, I forgot that I have this kind of gibberish date here that it doesn’t really like and I can filter out information by simply choosing to take out this null column and boom. I’m good. Okay, so I do this sort of same thing with my order method table. This is a basic one. I’m goanna bang this out.

32:33
Remove those columns rename this guy.

32:42
And then I’m going to do the same thing on my orders table. So you’ll notice that I want to I have my order cat-boy order Header information. I didn’t pull that table in I’m going to do the same expand functionality and I’m going to have it pull in all that information. So I have a nice orders table without having to do a whole bunch of extra work.

33:06
And then the last one is my Branch table. I’m not going to do anything to the branch table except rename it and might have something that I’m feeling fairly good about here, and I’m going to go back to my home table.

33:20
And I’m going to choose clothes and apply and what this is going to do. It’s going to take me back into Power BI desktop and it’s going to fly those query changes and here I can then go to the modeling tab in Power BI takes just a second.

33:41
And you’ll see my star schema starting to take form here.

33:47
Okay, so I’ve got my orders got my order method. I’ve got my product and then it always likes to hide some stuff off to the side here. And this is where I can sort of clean this up finish creating that star and so I’m going to go ahead and I’m going to drop this over here Order date on Day date to join that up again. There’s no there’s no primary foreign key relationship there. I can define composite joins. There’s a whole bunch of things I can do here.

34:12
I’m going to join product number 2 product number it’s going you take a look at that. I can set the cardinality take a look at what those columns look like together, but I’m going through the process of organizing all this information and then I can do some formatting right. So when I choose for example unit cost the format here is an in currency so I can do a search and do a Ctrl click on this and do formatting data typing assign roles to it where they haven’t been automatically detected by the tool. So I’ll double-click these guys.

34:46
Change their format currency.

34:55
And then at some point here, I’m ready to start a test this out and I can toggle to the visualization pane to actually create a visualization. You can see all my different visualizations here. I’m going to click a matrix one under my product. I can create hierarchies. Let me stretch this out just a little bit so it looks better.

35:24
I just drag and drop these guys onto each other. So same thing like in Tableau or creating a navigation path in in Cognos, so I can add that hierarchy to the visualization.

35:38
I can go and drag my current year to the columns.

35:43
And then I’ll go to orders.

35:46
And I can drag my unit sale price to the value.

35:52
And you can see it sort of spinning off in the corner here where it’s actually doing that SQL query and returning my results that to me and I can I have these little plus and minus just like I did in in Tableau that allows me to expand or collapse and I can see that putter is again or $100,000 $200,000 ninety eight dollars and forty four cents in 2010. So that’s sort of my sanity check of the data. Now keep in mind that I can create calculations in here.

36:19
So either a quick measure I am presented with the dialog box or I can add in that calculation or I’m actually going to jump over here to me to my finished turkey and pull that out of the oven. But if I look at for example gross profit here.

36:40
If I create a new measure, it shows me the m-code here. Okay, and I can type that code in.

36:50
Let me select that again so you can see it better.

36:57
Sorry about that. So this is DAX code that were writing here and I can I can type into this and create this calculation myself. I created it before in the interest of time. I’m not going to go and type this out but it has the same type of head functionality all the functions and all the things you’d sort of expect to have in that environment now DAX is the is this digital or sorry data analysis Expressions that is has a very wide array of basic functions time.

37:27
Functions and some enormous amount of complexity and sophistication that you can get into in that particular language. Alright, so those are sort of all the basics there are some interesting things to observe about Power BI that are sort of unique our aggregate awareness for starters.

37:48
So they have some really powerful functionality in here where imagine my orders table were billions of records against say a Hadoop Source, or maybe I’ve got a ton of history in here and a very granular I can create another version of this orders table that perhaps rolls up to a week or a month or maybe at the product subcategory level and then I can manage the aggregations and tell Power BI that. Hey, I’ve got this aggregate table here.

38:16
So if you can satisfy the query in one of your visualizations using that do that first and then if needed to you can go to down to the source data you also have Different capabilities here in terms of ability to define the storage mode. So when I initially connected it said do you want to do an import or a direct query? I can also Define that as sort of at the at the table level right? And this will vary your mileage may vary depending upon the data source, right and what in what options you have there?

38:48
And you even have dual where they’re sort of a hybrid approach of storing some of that in that tabular model versus doing live queries and then once you Have all this done just like in Tableau and in Cognos, you can publish this you publish it up to Power BI or your data sources can be certified and reused by the organization. There’s also robust security that you can apply here on these including at the aggregate level.

39:14
I think one of the standout things about Power BI is really that there are tons of transforms and modeling capabilities here and they are there are two tools but they’re integrated pretty nicely and Sort of use that as a caveat or as a defense for some people might want to fight me on what I’m going to show you in a little while in the form of Tableau prep. So that’s kind of the basic modeling demonstration across those three tools. But now I wanted to show you some more of the maybe more advanced capabilities and in this is in Power BI and Tableau because you can’t really do a lot of the transforms in Cognos.

39:55
So imagine and I forgot to open this up before this thing started but imagine you have this is a common scenario, right? You have your business analysts who have Excel reports you get tons of those in the business. There’s a nice little report that shows my equipment by my supplier my quantities by month for a particular year across my product classes and different manufacturers and I’ve got little spark lines and other stuff in this report.

40:22
It’s a great potential data source, but any of you who have built reports and things no, that out-of-the-box. This is this is you know, highly suboptimal. So being a good little citizen data scientist when I want to do is I want to add this data source into my visualization. And so I’m going to go out. I’m going to go to get data.

40:47
And it’s going to bring up the window for me and this time I’m going to choose Excel.

40:53
And it’s going to point me to the file system. I’m going to choose my equipment by supplier.

40:57
It’s going to open up the navigation window and show me all the tabs in the workbook. In this case.

41:03
I’ve got three and I’ve got my Oops. I’ve got my equipment by supplier, which I’m going to select and you can already tell before I open this up that this is kind of a hash right? So I’m going to go ahead and click transform data, which again is going to bring me into the power query editor and I’m going to use some of these cool transformation capabilities that exist here. So again, I’ve got a bunch of steps here it tried to do some stuff for me. It changed some data types and promoted the headers, but that’s not really working. Right because my column headers aren’t right. I need to take a couple items out of here.

41:35
So I’m going to remove the top two rows.

41:41
And I am going through this quickly. I understand. There’s just a lot of content so don’t attempt to follow along just enjoy the ride and then I’m going to go ahead and use my first row as headers. So you can see these nice transform capabilities is keeping track of this stuff. And now I’ve got my class and my manufacturer and I’ve got all my months here. So this is starting to shape up pretty well. Now. What I want to do is remember, there’s all those blanks there in these rows. This isn’t good for reporting. So I’m going to go to my transform tab.

42:06
I got this fantastic Phil functionality and it’s going to fill this stuff down. So I’ve got so I have a label for each one of my rows and that’s fantastic. Now, what I need to do is I want to get rid of everything that’s not actually one of my classes.

42:23
So there’s some nice text filters in here and I’m going to say, you know if this does not equal class then I want to take it out.

42:35
And so it’s going to take out those headers that I don’t need and I’m going to do the same thing here where I’m going to filter out all those totals and I’m going to say well if it does not contain so it’s basically going to keep Anything that does not contain total, so it’s going to take out all my totals now. I’ve got a nice-looking set of rows.

42:55
The last couple things I got to do is I’m not interested in these last two.

43:00
Remove the columns and I have to unpick visit this data, right because in the columnar format like this, I can’t really report off of it. So there’s this really handy on pivot column capability. Now, it creates an extra record for each one of these and I can call this month and I can call this guy quantity and then just like that. I’m able to drop this back.

43:27
It shows up in my model as a data source.

43:33
It’s going to warn me because I pulled in another data source going to say hey. Do you are you sure you want to do this? It’s going to apply those query changes and pull it in and then there’s one more change. I want to make to this thing.

43:51
And that is it still thinks this is text. So I’m going to change it to a whole number.

44:00
And then it’s going to change the format of this from a text and treat it like a measure so that it actually it actually shows up the right way. So and it’s going to change the default aggregation to a summary. So I’m going to go out here. I’m going to create a new tab. And again, I’m going to throw that that Matrix in here.

44:28
I’m just going to say class and manufacture.

44:43
My month and my quantity All right, and it see it automatically created a hierarchy for my didn’t have you didn’t even have to drag and drop those on there so you can see for example in January 27900 32 that lines up with my January figure for armatures now in Tableau to do that. You have a tool called Tableau prep and some of you may be kind of you know saying that’s not really fair. This is an ETL tool and to be fair. It is an ETL tool and it’s very specific.

45:18
Vic to the Tableau environment. However, it’s very much in the ecosystem of your Tableau users. Anyone who has a Creator license is going to use is largely going to is you’re going to find them using Tableau prep more and more that said it does have two it does have two output either to an extract and or a flat file they are adding database functionality to that.

45:45
I believe this year so, it’ll become I think a lot more usable as a As a general kind of called lightweight ETL tool but it’s really nice because I can attach to this file now to be in In fairness to Microsoft. They do not have this Phil functionality. So I did have to create a different version of the of the Excel sheet. So I created it and all I did was like a good little data scientist. What would you do if this did if I didn’t have full functionality right? You just go and copy this down here and you cheat right, but you get the job done.

46:18
So I’m going back here to Tableau prep. And what I do is I Source data. You can see all the various Source types. Not quite as many as you have in in Tableau or in Power BI but they’re expanding that all the time the tools about 2 years old and then you go and you add steps to it, right? There’s cleaning steps aggregation steps pivoting join Union script and then output steps and it creates this nice work flow. So I’m going to add a clean step here whereupon Tableau immediately starts to profile the data that exists in my source.

46:48
Source and again, it doesn’t really get it right the first time write my column headers don’t look good. But as in Tableau desktop you have this nice data interpreter, which is going to go and do a couple of things that we automate that we had to do manually in Power BI so it deleted those top two rows. It’s kind of ignoring the charts in the spark lines and it gets the column headers, right? So I’m already a little bit ahead of the game here. Now.

47:12
I’m going to do the same thing where I just want to keep my specific classes here or I have my housings and my Hubs and my Sundries and I’m going to right click this say keep only those guys and I filtered out the extraneous stuff and the totals.

47:36
Now I’m going to go over here to the end. I know I don’t need this guy to right click it and choose remove and I’m getting pretty close here. I’m going to add one more step to uncover it the data. So by adding a pivot step it simply takes all the columns throws them over on this left side here. I uncheck the ones that I don’t want pivoted and I throw all my months over here and it does exactly what it did in Power BI where it creates pivot one names, which I can do.

48:06
Click and call month and it has your pivot one values where I’m going to double click that and call it quantity. And then my last step is to add an output step where again I can choose to I want to put out an extract or a hyper extract or a flat file and the default. Is this sort of TDE and then all I do is I run the flow.

48:31
I’ve already done this before.

48:34
And just so you can see while this thing is running. This is an example of a of a sample data flow. That’s much more sophisticated. Right? So you have multiple different data sources

48:48
trying to resize the window here and you’re combining those data sources, you’re cleaning them along the way you can see all the different changes that have happened. It’s visually profiling the data and you can do some fairly complex things in here and interact with your data. There’s some nice machine learning and fuzzy logic capabilities in here and then from within Tableau I go back to add new data sources and I can add a new one.

49:19
And this one I’ll add.

49:24
I’m just going to open up my files here.

49:29
Go over to my Tableau prep repository.

49:34
Choose my data sources and you can see my output here. I’m just going to open that up.

49:44
And it’s created a whole new, you know metadata pain form here where I can choose my extract and there is my nicely formatted data. I can go out here and I can just, you know, drag and drop my columns into place.

50:04
And there you go, and if I add my totals At all my subtotals you can see 2792 is the number I get for January. Alright, so that is Tableau prep. And that is the end of the demonstration portion here. So I’m going to bring it back. Oh wait, I’m sorry. There’s one other thing. I wanted to show you on the Power BI side and I don’t dive into this, but you have what are called dataflows.

50:47
And dataflows are Power Query on the web. So in powerbi.com you have a whole host of sources so within a workspace you have a set of connectors now, it’s not the same.

51:01
It’s not quite as wide of an array of connectors as you have with other with Power BI on the desktop, but there’s some interesting tricks you can get around like you can do blank query and you can pay stuff from desktop into this and they’re always enhancing the Ality here, but basically here’s one that I have opened. This is the equipment by supplier spreadsheet that I’m able to attach to via either SharePoint or a direct connection using the on premise gateway to that spreadsheet and you can see I have a lot of the same functionality. So here’s all the steps that I went through you can you know transform the columns you have the capability the transposition I was able to do everything that I showed you in Power BI Desktop to get to my kind of finished product here.

51:47
In Power BI for the web in the form of a dataflows and then this data is stored up in the Azure cloud again as a tabular model and can be reused in your organization. All right. So now I mean it I’m really done with the demonstrations. So that was a whirlwind tour. Hopefully you all were able to follow along with that and that was useful.

52:09
I’m going to revisit now that comparison of the basic modeling and transformation capabilities again from a basic perspective if you’re doing you know joins and unions or data typing and splitting and stuff like that. You can get to it checks the box on all these splitting in Cognos Framework Manager. For example is you have to use up a calculation to do that right kind of old-school stored procedures? You can’t leverage those in Tableau Prep yet. You can’t use those in Power BI dataflows yet. You can’t do it in Cognos data modules, you know yet and Tableau Prep. You can’t create hierarchies for example, but all the basics are there.

52:47
From an advanced perspective some of the transformational type stuff that I was doing you start to see a little more differentiation there and this list is not comprehensive that call it, you know semi or quasi comprehensive but it covers the basics here.

53:03
And again you can do most of that Cognos Framework Manager doesn’t have a lot of transformation capabilities data modules don’t have a ton of transformation capabilities, but they’re getting there and what I did is I highlighted some of what I thought were the product strengths like Tableau Prep has a lot of great intelligent grouping and cleansing algorithms like grouping things together on pronunciation. So they have some really neat stuff in there. That’s cool. They do automatic binning and Power BI has some really nice as I showed you that functionality and that pivoting it on pivoting is very powerful some of this depends on the data source to though right you can only do certain transformations on certain data

53:47
is and search capabilities are only available like a great example across all the tools is if you’re using a cube a cube is what it is and you can’t do a whole lot with it. You kind of give them the cube the aggregation stay where they are the though the hierarchies that the same you can’t really play around with that stuff for the most part across the board security is a particular strength. I think in Power BI Desktop in dataflows in that combined with the aggregate awareness. You can have those aggregates.

54:17
Respect security that you have applied there. So I think it’s a product strength, but you see with the rapid iteration of these tools right Power BI comes out with a release every month and Tableau comes out with a release, you know, four times a year prognosis even accelerating their pace of development where you see three or so releases from them a year. And so this list it could very well be out of date tomorrow or might be out of date as I speak.

54:42
And then lastly there’s some more there’s something that didn’t fall into either one of those It’s but it’s really kind of the query storage options in that you have just live capability and you have live or data set capability in data modules and you have more flexibility more or less flexibility depending on the type of tool you’re using and there are various limitations to those things. Right? When you ever you create an extract or an import or whatever you want to call that you’re landing the data somewhere else. So there are security considerations space considerations latency connections.

55:17
Considerations and then size limitations on those on those things. So they’re all important considerations that you know again, that’s something we get out of bed and do and help people with every day from a lineage perspective, right the governance and the Enterprise capabilities of these tools is something that we’re seeing being enhanced all the time Cognos has had lineage for a long time.

55:39
So, you know where your data is coming from and you trust it data modules have the same thing Tableau recently introduced explain data, which provides this nice. Here’s where my data came from and it gives you some nice English language explanations. When you have that people tend to trust the data and then the introduction in the leveraging of artificial intelligence machine learning fuzzy logic modeling and transformation. So I gave you a quick peek of the intent over in Cognos data modules at the beginning and then again Tableau Prep has some really nice stuff around the sum of the data cleansing and stuff like that. And if version control something that’s important to you FM

56:17
has that built in Tableau desktop does some nice versioning as well. So the summary of all of this the takeaway is that there’s a lot of ways to get from point A to point B, and you can probably get there with any of the tools. It’s a question that is going to be unique to each organization of among other things. What’s the analytics culture that you’re trying to instill or support in your organization?

56:46
Is it a service or is it more you know centralized or is it a hybrid there or the skill sets of the folks in your organization’s right of a centralized or do you have people that are highly technical and very data savvy in your organization? What tools do you have? If the tool choice has already been made, then you’re going to want to optimize based upon what you have and then of course, what are you use case in your business requirements and one of those data sources as I imagine, right?

57:14
So as I mentioned before the data sources are going to dictate what you can do in the tools how they scale and all sorts of other things. So those are just kind of five of the things that are that are going to determine what you use and how you use them. So hopefully you found that presentation useful stick around. I’ve got some next steps for you in terms of here some great free resources where we have got some great webinars where we do comparisons across these different tools.

57:43
So enterprise security Tableau versus Power BI and we do it. Some dashboard in comparison between the three tools we do offer training as an organization across all three of those tools including data modeling and transformation classes in each one of those platforms. So and are our instructors are trifecta compliant or fluent. They often times or in most cases know at least two if not three of the tools which is really helpful in organizations where you know, folks are probably using more than one tool.

58:16
Oh and we can also provide ad hoc support to you in the form of on-demand support bucket of ours where we can bring experts to help you get that data model and transform so that you can really leverage it powerfully and accurately and have it adopted in your organization with appropriate governance and security and the training to back all add up a couple of quick slides on Senturus and who we are we are a nationwide consulting firm exclusively focused on BI.

58:47
You have a depth of knowledge that is unrivaled in the industry our clients know as for providing clarity from the chaos of complex business requirements, myriad disparate data sources constantly moving targets and changing regulatory environments. We’ve made a name for ourselves because of our strength of Bridging the Gap between it and business users. We deliver solutions that give you access to reliable analysis across your organization.

59:11
So you can quickly and easily get answers at the point of impact in the form of the decisions you make and the actions you take we offer a full spectrum of BI services from as I mentioned, you know architecture designed to landing and preparing your data supporting hybrid environments that we find many of our customers and whether that’s migrations or enterprise security on-perm to cloud managed services things like that. We have software that enabled by mobile BI for example Tableau and Cognos and Power BI and Cognos environments or platform migrations.

59:45
Our consultants are leading an experts in the field of analytics with years of pragmatic real-world expertise and experience advantaging advancing the state of the art. In fact, we’re so confident in our team and our methodology that we back our projects with a 100% money back guarantee that is unique in the industry. We’ve been doing this for nearly 20 years focus exclusively on business intelligence. We’ve worked across the spectrum on everything from Fortune 500 to mid-market customers solving business problems across various functional areas from the office of finance, sales and marketing, manufacturing,

1:00:16
operations, HR and IT. Our team is large enough to meet all your business neat analytics needs but small enough to provide personalized attention. We invite you to visit senturus.com/resources to expand your knowledge.

1:00:31
There are hundreds of free resources on that site ranging from webinars to blogs on all things bit fabulous up-to-the-minute easily consumable blogs some great upcoming events that we encourage you to head over to our site and register for use The new Cognos KPI capability and relative time structures really exciting capabilities that really help you leverage some powerful KPIs and make that really easy and then we got another great one. I mentioned Power BI dataflows and certified datasets. We’re doing a full webinar on that in just a few weeks. So go register for that and we’ll be adding things in February things around Snowflake will be doing a demo of their cloud data platform will be talking about four ways. You can prep data and Power BI so I’ll be diving into that more

1:01:16
deeply and we’ll be talking about complex aggregations in Tableau. So we invite you to join us for that. We have a complete spectrum of training across Cognos Power BI and Tableau. We’re ideal for organizations that are running multiple platforms or for those pivoting from one to the other and that ranges from in-person tailored group sessions to individual one to a few or one-to-one mentoring instructor-led virtual courses and self-paced learning so we can provide training.

1:01:46
All of those different modes across all these different platforms leveraging our world class instructors some additional resources from us, you can find unbiased product reviews technical tips Insider viewpoints, etc. by just visiting our website so head on over there. And then again, it’s top of the hour here.

1:02:08
Please put your questions in the question log, and we will make sure that we answer all the questions try to make them as clear and concise and with appropriate context as possible. We will post those along with the recording and the deck in just a few days or a couple of weeks. Maybe once we get that edits and stuff done. So with that I’d like to thank you for your time today. Thanks for attending. My name is Mike Weinhauer. How are you can reach me at the contact info below. We encourage you to reach out to us if you have any analytics training or otherwise needs at our website.

1:02:46
At 888 601 6010 or at info@senturus.com also reachable on most social media platforms, and we look forward to seeing you on the next installment of our knowledge series. Thank you and have a great rest of your day.

Read more