Accelerate Report Migrations from Cognos to Power BI & Tableau

×

Todd Schuman
Practice Lead Installations, Upgrades and Optimization
Senturus, Inc.

Todd has over 18 years of business analytics experience across multiple industries. He also regularly connects with the IBM product development team and has been in the forefront of the Cognos Analytics upgrade and installation process since it debuted. He lives with his wife and two daughters outside of Washington DC, in Virginia.

Read more

×

Q: Would you recommend we import tables depending on what the report needs rather than trying to build a generic data set that multiple reports can use?
A: This depends on how broadly the query subjects in your existing model are used by the reports that you wish to migrate.  We often see complex, “kitchen-sink” models in Cognos that have evolved over many years, where only a small portion of the model is currently used for a particular collection of reports. In such cases, a more agile, iterative approach can give you quicker migration results by starting with only a subset of the tables from the existing model and then building on that over time.

Q: Is the process as easy to move from Power BI to another tool as it is to move from Cognos to Power BI?
A: The Senturus Report Insights app currently only supports extraction of the underlying report structures from Cognos as a source.

Q: How easy is it to set up Report Insights? Are there best practices to determine which reports are best to migrate to another system like Tableau?
A: Setup of Report Insights is performed by a Senturus consultant in collaboration with an administrator of your environment. Determining which reports are best suited for migration to a particular platform depends on the structure and complexity of the existing reports and underlying data. Unfortunately, there is no one-size-fits-all approach to this analysis.

Q: Is Senturus Report Insights free?
A: No. Report Insights is currently available as part of our migration kickstart engagement and for larger, custom projects.

Q: How is the Cognos report generated in the demo?
A: The report is generated using our proprietary Report Insights database and a custom Cognos data module. We extract and analyze the underlying source data from the Cognos content store.

Q: Does Senturus Report Insights automatically convert to Cognos reports to Power BI reports?
A: No. Report Insights provides a summary of the information that is needed to create an equivalent Power BI Report, but automatic conversion isn’t available due to the complexity of feature-set differences and the closed/proprietary nature of PBIX files vs. the open XML schema used by Cognos reports.

Q: Does the Senturus Report Insights work with Active Reports?
A: Report Insights doesn’t include specialized support for Active Reports objects; however, it can still analyze and summarize the queries and underlying logic for any standard Cognos report object that is used within an Active Report.

Q: Does the Senturus Report Insights show calcs/logic on columns including complex if then else statements?
A: Report Insights shows details of calcs/logic and if-then-else statements for data items within reports; however, it does not currently show calcs/logic embedded within the underlying Framework Manager model.

Q: Is it possible to schedule reports to run from Power BI and email them out to users?
A: Microsoft just rolled out new bursting functionality. You can talk to one of our Microsoft consultants to outline the distribution options with Power BI. Our phone number is 800 601 6010 ext. 1.

Q: How are Cognos reports with prompt pages handled in Power BI during conversion?
A: Power BI recently rolled out additional bursting capabilities.

Q: Can we run the Senturus Report Insights ourselves?
A: Setup of Report Insights is performed by a Senturus consultant in collaboration with an administrator of your environment. After the initial setup is complete and the insights database has been populated, you can run the tool to explore the reports of your choice.

Read more

×

0:05
Hello, everyone and welcome to the latest webinar in the Senturus Webinar series. Today, we’ll be covering report migrations from Cognos to Power BI and Tableau. So welcome everybody, wherever you happen to be. Before we get started, I just wanted to do a quick sort of administrative stuff. Go over the GoToMeeting Webinar or the GoToWebinar control panel. Feel free to use the control panel throughout this session today, to make this session interactive will respond to some of your questions while the webinar is in progress.

0:41
And if we don’t reply immediately, there will be a Q&A session at the end of the webinar, and we’ll also follow up afterwards, if we’re not able to get to the questions during our Q&A session.

0:56
To obtain the presentation with a question we get very often is, can I get a copy of the deck? The answer is yes. And you can also see a recording of the video. Those are available, apps and tourist.com Senturus.com/resources that you can also click the link that will be posted into the chat window. I’d encourage you to really look around in the resource Library, because in addition to this presentation, you’ll find a wide variety of other resources on business Analytics topics. We have a very rich collection of past webinars and other material that you might find interesting there.

1:38
Today’s agenda, First of all, go through while we’re doing the introductions in a moment, then we’ll cover the report migration progress from Cognos.

1:49
Current process for inventory in your existing reports and models look at some better ways to approach that, ways to accelerate the process and simplify the challenges of migrating reports to another platform. You may be thinking, well, why not do this manually? If you’re familiar with the Cognos reporting tools, you know, that, even though you can get the report information manually, that’s pointing and clicking and looking at individual places. So, we’re going to talk about a better way to approach that and help speed up the process of getting reports out of Cognos And into a new platform. We’re going to do a live demo and then, we’ll talk a little bit about and, you know, just our company in general, followed by a Q&A session to answer your questions.

2:43
Today’s presenter is Todd Schuman Todd is our practice lead for installations upgrades and optimizations. Todd has over 18 years of analytics experience across multiple industries. Here regularly connects with the IBM product development teams, so he’s always on top of the latest, greatest details of IBM Cognos. He’s been on the forefront of the Cognitive Analytics upgrade and installation process since it debuted with his wife and two daughters outside of Washington, DC in Virginia.

3:17
I’m Steve Read Pitman, I’ll be moderating today. I’m the Director of Enterprise Architecture and Engineering here at some tourists. I wear a lot of different hats here, but generally they cover management of our engineering IT and managed services groups.

3:35
Before Todd launches into the presentation, we just want to do a quick poll. So I’m going to open up the poll here, and the question is, which platform are you currently migrating to, or looking to migrate to? And we’ll just leave that open for a little bit.

3:53
It’s got answers are coming in. It looks like about a third of you are looking to go to Power BI, about a third to tableau. And about a third of you who are not currently planning a migration. So, I’m going to keep that going just a little bit longer. Last few of you get your answer them.

4:14
I will say that historically we’ve seen, for a long time, we saw a lot of movement to Tableau, more. Recently, we have been seeing more migrations to Power BI and historically Senturus, great deal of migration work over our 20 year life span.

4:36
But, we have seen a bit of a change or shift in what those migrations look like in more recent years. In the past. It was often a migration project just to do Cognos upgrade across major versions of you who’ve been around the world for a long time. Are probably familiar with some of the biggest challenges in the past with things, like migrating from an impromptu into report, or report Cognos or even more recently? Cognos 10 to 11? Alright, I’m going to go ahead and close out the poll here.

5:11
So, we have, looks like 35% of you going to Power BI, 26% below. So, pretty even split in terms of your destination platforms.

5:26
We’re going to go ahead and start the presentation now, and he’s gay, although today he’s going to focus on moving to Power BI. The concepts here actually also apply to moving reports to Tableau, and, with that, I am going to hand it over to you, Todd.

5:46
All right. Thank you, Steve.

5:48
Can I jump right into it because I’ve got a lot too, both you know go over and also demo today. So, what we wanted to demo and show you was, you know, if you’re looking to migrate off of Cognos or even if you’re just trying to gather requirements, it’s very difficult to get all the information out of the reports. Out of the models we’ll take a look at what that’s involved. So, a simple request.

6:14
You know, in this scenario, you’re asked, Hey, we want to, you know, recreate some of these reports and another tool, whether it be Power BI, Tableau, with any tool you really have, someone’s going to ask you to go ahead and rebuild these reports and so, there’s a good chance You didn’t build them yourself. You, someone else did.

6:35
So, you’re going to have to go and find out whether it’s through Audit Reports, you know, what reports are being used the most Which of these reports are essential that we have to get migrated over? You have to then find the reports it’s gotten much easier with Cognos 11, the search capabilities work better, so it’s a little easier that is there needs to be defined reports and some of the older versions of Cognos.

6:56
You could have dozens, if not hundreds of folders and sub-folders and digging through those, and finding that it can meet time consuming.

7:03
Each one of those reports, again, you have to kind of go and check the property, what packages this leveraging means? It’s opened up the reporting, go and grab the specs. There’s different pages and queries, and you eventually kind of build out an Excel or Word document. You know, some kind of requirements document that had, you know, the name of the report, the package. Amina, on page one you add this and it’s tied to this Query, and it’s using this customer name.

7:28
And it’s coming from this namespace in the framework manager model. So it was just very, very time consuming my opinion. I’ve had to do this over and over again.

7:37
So, know, once you kind of have that information, you can then begin to do your migration.

7:44
So, it’s sort of, whether it’s getting Tableau or Power BI, different tools, all kind of have the same thing. The first step is, you got to create your underlying models with tables. Do I need, what are the joins and the relationship between the tables? Do I need to decimal calculations or Filters? Is there business logic and terminology I have to rename and apply here?

8:04
You’ve got your model, it looks good, and then you can go and actually build the reports and dashboards? Do I need multi pages? Do I need the layout to have multiple objects on one page? What kind of containers Visualizations do I need, you know, the underlying queries that Drive does, what do those look like? It’s all this information. It takes a lot of time and effort to kind of capture and then to build it out. So, right now you know, what’s with the way you have it in the tools you have today.

8:31
You know, starting with the model, these models can get very complex very quickly, even the goes sales.

8:38
Great outdoor Warehouse that comes with Cognos. If you’ve ever taken training or genetic samples, you’ve probably been exposed to this. Imagine it to be a pretty simple framework manager model, but it’s actually very complex. There’s a lot of different namespaces in here, lots of different layers. There’s parameter maps, multiple packages, a lot going on. And especially, when you’re looking at reports from the presentation layer, things I’ve been cleaned up and renamed. It’s difficult to figure out, OK, it says product line. Where is that actually coming from as far as the database table? And what field is it using?

9:15
Where the relationships I need to go and make sure that when I bring that into report, I have all the other information tables joined correctly, so going through this, its very time consuming.

9:27
Even finding the right tables, you’re still gonna have to do multiple clicks.

9:30
You have to right click and go to the details, the Properties, figure out what the SQL looks like, go and look at the relationship diagrams, or modify the relationships. And see, OK, is order number, is joining the order number in this detail, Just a lot of information to capture.

9:46
Very similar, on the reporting side, we’ve got different areas of capture here. So each report could have multiple pages. You know, one of the questions I get when people, or, you know, speaking out some reports of Alzheimer’s, you know, how long it takes to build one report? Well, one report can be extremely complex, but 10 different pages, dozens of queries, and joins, and unions. Or it could just be a single page of the single query.

10:09
So it’s very vague in the sense of, you know, a single report, but in the, in the sense that, you know, we have multiple pages, if we want to make sure that we capture all of the information about a single report. So how many pages are in there? How many queries? How many classes? Other variables? All this information.

10:26
So, a single page, again, can have multiple objects. I can have a table with you know, a visualization. A chart across tab lists no real limit a restriction on what I have in there. So it’s pretty much, you know, as open as you want it to be.

10:43
And depending on how complex you may have a lot of inventory, you do on the reports, same thing with a query. Each one of those, those sort of data containers can have its own query, or they can share a query. We want to make sure. We know which ones are mapped to, which.

10:58
And, obviously, inside the query, we can go and navigate to the query, Explore. You’ll see each one, and you can click on them. And you get some high level information. It tells me you know, product line, year, revenue, but again, I don’t really know the full paths to the namespace, the query subjects. Even though filter itself, I can see there’s a filter on this query. But it gets truncated off, depending on the length of it. So I don’t really know what the right side of that filter is, so I need to know more information and they can get, I can double click on these very each time. I’m clicking on one of them. It’s going to take more time. It’s, you know, additional clicks to open that window, to close it out.

11:33
I had to take a screenshot or copy and paste it into some type of documentation tool, aggregations, another one calculations, you know, things like that, all exist in queries.

11:45
Each data item, as I mentioned, you know, you have to kind of click into it. You kinda get this screen here, which is very important, It’s going to tell me what namespace is referencing what queries subject, and then the actual name of the products, which is even not the whole. Picture itself, again, product line and products, you’re not gonna find a table actually called Products.

12:02
You’ll see some stat looks similar, there’s Products, there’s dim, Product Line, product type Look-ups. We’ll see some of the actual raw tables in a little bit. But again, this is only a piece of the puzzle here, so I’m just kinda getting more and more information. But it just takes a lot of clicking around to get to this information.

12:20
Sat there with my filters, you know, I kinda double click it. I can now see that there’s a hard coded filter on the country being sent to the United States. Again, this is something that, you know, you may have a single filter, no filters, or dozens, and dozens of filters, which I’ve seen before. Some are hard coded, and we’re going to be parameter based, you know, we need to know and make sure that when we recreate these reports and other tool, that we have these photos captured correctly.

12:43
So, after kind of, going over the, it’s the old way I would say, Are, you know, the way that we’d done it in the past? Is there a better way? Are we able to work smarter and actually kinda capture all this information? And, the answer is, yes.

12:58
If you know anything about the way that Cognos sort of stores information, it’s all basically XML based, both the reports and the models, the packages that you publish out. It’s all based on XML language.

13:12
So all that information can be kind of scraped out and then sort of map together in a way that we want to see it in reports.

13:20
So, by kind of going through the content store database and grabbing all these specs in the models, and creating some containers that kind of arranged this the way we want to see, I’m able to kind of get by package by package, a list of all the reports that are tied to a package summarized view of each report where I can see, you know, very quickly, all in one screen, how many pages there are, what’s on each page. What each of those objects is, is reference. As far as the queries what’s inside the queries. As far as the expressions, the Filters, the Aggregations, I’ve also got the ability to kind of click on many of these different links and get lineage and drill into and see, you know, what layers is it going through in the Framework Manager Model.

14:08
So a ton of information is easily consumed here in a single page, and you can get these, you know, start running rapport. And that’s what we’re gonna do in the demo.

14:17
So, on the modelling side, as well, you know, looking at in the far right corner of this screenshot is, you know, the typical framework manager model houses.

14:31
Again, we’ll look at the FN mile live in a minute, but there’s, there’s a ton of information in here.

14:37
So I picked a, wanted to go to our retailers, warehouse, sample packages, But the model itself contains a bunch of other dimensions and facts.

14:49
And if, for the purpose of just recreating these reports that were flagged in my Report, it’s going to minimize the amount of tables that I need. It’s gonna say, hey, for these tables to exist, you don’t need to bring in these hundred plus or more tables.

15:03
You really only need these seven tables, and here’s how they break down. You’re referencing from the package, the sales query, and these query subjects, and here how they are actually tied to the tables that make them up.

15:16
Usually, it’s a 1 to 1, so now you still have a merging of employees and regions, things like that.

15:22
Um, give me that list and this is gonna make it much simpler to figure out, what are the foundational tables I need in order to recreate these tables. Again, these are all going to be drilled through links that kinda tell me, hey, you know, what does this join to this, table, in case I’m having issues creating the relationship?

15:41
Excuse me. And you can kind of see, OK, I’ve got, I didn’t mention table here, it joins to this. Here’s the keys, is going to kind of give me almost a blueprint of, Here are the specs that you need to create the report, here, the tables you need. Here are the relationships.

15:55
It all come together in one single Cognos report that lets me take that and, I can go into their tool and then, bring in the tables, bring in the relationships, All the information I need and then build out those reports without having to dive through a bunch of different screens in the Cognos tools.

16:11
So, this is just a demo that we’re going to look in a minute but, again, I’ve got a standard kind of Cognos report here across Tab, with ears and product lines.

16:22
And then, I’ve got a bar chart showing that by year was able to kind of very quickly, within just a couple minutes, create that model in Power BI with this tool, because the numbers to tie out exactly and basically build out the similar type report in Power BI. So, that said, I’m going to now kind of jump into a demo and show you first the reporting tool. And then just how easy it makes it for me to go ahead and build out a new report.

16:46
So, let me jump in to Cognos and let me close out. Smiths reports I have open.

17:00
OK, we are on Level 1 7 that just came out a little while ago, so we don’t have any issues, but never know what the live demo. OK, so I’ve got my report, it’s called Report Insights.

17:17
And the first thing it’s going to do is it’s going to show me all the available packages that I have in my content store.

17:25
And I can pick any one of these. And then depending on how you have it published out, more often than not, you may have published out one to a test area. Or you may have it in your My Folders. You want to make sure that we picked the right version. So if you have multiple instances of that package published, we want to make sure that we’re pointing to the right one.

17:46
So I’m going to pick this directory where I published it to and then hit Finish.

17:52
And the first thing it’s going to do is kinda give me a summary page.

17:54
It’s going to tell me, OK, here’s the inventory for the Go Data Warehouse Query package, here’s where the package exists, and it’s scanned to the content store and found five reports that are tied, or are pointed to this package. I’ve got an employee quantity sold by branch region.

18:12
Looks like there’s two copies of this multi page object report, Retail type, our product line, gross profit, and top level or chart, hit page down.

18:21
Here is the namespace of database mapping I mentioned, and then this is sort of a breakdown of any table Sometimes, You know, we’ll see employees into different query subjects. So, just to make it a little cleaner, we got a distinct list here, but this is always helpful. This takes a good amount of time to kind of go through the Fabric Manager model.

18:42
For example, no order method here, if I was to use that in a report, it’s going to be in my presentation there. So, I’ve got FM here.

18:53
If I go to my sales and marketing, sales Cleary, here is quarter method. This is a shortcut and this is sort of you know based on best practices for modelling out an AFM model.

19:07
But I had to kind of say, OK, where is this going to? Let’s go to the target.

19:11
OK, it’s in the Business View, where did it go?

19:17
Good at the target.

19:18
Sorry, it’s down here.

19:20
OK, then S border method is down in here, I can edit it, and I can see it’s going to SOS Border Method Dim, which is in my database layer.

19:30
I can also right click on it here and get that.

19:35
Actually, the database layer first.

19:38
I think it’s in here, method here, this side to go and edit this. OK, here’s my SQL that’s being used to bring the table in.

19:49
I don’t have any calculations or filters.

19:52

And I can right click on it and show me the context for, and I can see, it’s got a relationship, one too many, on the SOS sales fact automatic key. So again, this is just one table, but there’s a lot of time spent kind of going through these levels, these layers and FM, just to kind of get this information.

20:09
Um, we’ve kind of grabbed all that information, summarized it.

20:13
So now I can just click on SOS quarter method dem, and it’s going to tell me.

20:21
We found in the metadata that it’s got joins to the following tables.

20:27
And I could see here’s SLS quarter method demo

20:30
It’s join, too, just dist return items fact an SOS sales fact, and here’s the key that it joins on an order method key, which is what we saw right here.

20:39
Order to key ordered by the key, I’ve also got this other table, which I’m not really concerned about, because it wasn’t in my list. I’m not using anything from this return, guidance facts. So, again we’re kind of focusing on not bringing back all the metadata, which we could use this tool for, but this is more about optimizing and trying to capture, just around specific reports that we want to instantly move over to another tool, so I can ignore that. I need these two tables, though.

21:02
So, yeah, sales facts in order method dim are going to be essential. And I’ve kinda got that information. All bubbled up here. Here’s my fact. Here’s order at the dim.

21:12
So, once I’ve got this, I can go and go into Power BI.

21:16
And I’ve got Azure database that I’ve loaded up with the created outdoors sample data. So, I’m gonna go ahead and just connect to that.

21:29
And I can I can see right here, the tables I need to go and get. So it’s just a matter of going through and finding these tables, which is going to be my employee dim.

21:39
I’m going to turn off the preview here. Just a little quicker. Get employee, Dan I need to go down to my go tables.

21:47
I need Go Region, and go time.

21:50
Check That Times.

21:53
And then I’ve got four from the SLS Schema.

21:59
So I need SLS, Order Method, and dim eight SLS Product Line lookup.

22:08
I need SLS retailer. And I need SLS sales facts. So I’m gonna go ahead and load them in.

22:18
OK, it’s going to take a second to go ahead and do a direct query for now.

22:25
I do it staying. It’s going to kind of evaluate the tables, bring them in. Detect any relation says, relationships for me.

22:33
And then after a second or two, I’ll take a look at what it did, and we’ll see a couple missing pieces to fill in.

22:43
OK, working on it.

22:46
And it’s done.

22:47
So let’s check out our model, it’s sort of bigger.

22:53
Collapse properties.

22:57
OK, so we can kind of see we’ve got a quasi I sort of star schema here with my fact table in the middle. I’ve got orders, retailers, employees, my time dimension.

23:11
Got two tables over here that didn’t get linked up. It doesn’t know how to relate this.

23:15
I’ve got the SLS Products Line Lookup, and I’ve got S or, sorry, go region dim.

23:23
So I need to know more about how do I enter integrate these with the tables that have already come in.

23:28
And again, clicking on these is going to essentially do that, just for me.

23:31
So I can click on the SLS Product Line Lookup.

23:38
And it’s going to show me tables that have that have a Joiner relationship to it.

23:48
So it’s telling me that there’s a SLS product demo that’s going to sort of bridge the gap between. I can keep drilling into this to see you know what the next piece of the puzzle is.

24:00
Product dim is joined to the SOS Sales Facts, so the SLS Sales Fact joins the dam on Product Key. And the SLS Product Jim joins to SLS products. Its line look up on the product line code.

24:15
So I’m able to kinda just click around and get the information I need.

24:18
One nice thing is that Power BI is actually pretty smart. If you attended any of our webinars or training, we’ve got Power Query and it can actually kinda figure this out on its own.

24:28
So this one isn’t too difficult to kind of figure out if I go to my product dim here.

24:37
And I saw all the way to the far right.

24:40
You can see it actually brought in the SLS Product Dim for me and said, hey, I found this table with some keys.

24:46
And I can choose, you know all the keys I want, but essentially all I need to bridge this table to my fact is this product keys So I’m gonna unsought everything and just select my product key.

24:58
Another issue with that goes, sales data, is that they’ve included all these different languages, which is great, but for the sake of kind of scrolling through, and just focusing on English for this demo, distinguished audience, I’m going to just remove everything, except for the, to end here, language. The way to just shift click, and I’m going to remove this column, just to make it look a little tighter.

25:21
Normally, with more time, I’d also kinda clean up some of the metadata. You know, I make this, you know, first letter capital, lowercase product line without the underscores and the English, but that’s, you know, just trivial stuff. So, I’m going tokind of leave that alone for now. I’m gonna go ahead and close and apply. Before I do that.

25:38
I wanted to say to the other, the other table that was enjoined was this go region.

25:45
Here it’s not actually going to be smart enough to kind of give me that information.

25:48
I got some tables here, but they’re all other fact tables. I got an SLS Sales Target Facts Market Retail Survey Market Promotion Plan, the promotional table.

25:59
These aren’t any of the tables that really want, so it’s not gonna be able to kind of help me out here like it did for my product table. So for now, I’m going to close and apply.

26:08
I’m going to go back to my insights to kinda get some more information. Ok, so it’s going to apply the changes to the product table. I should see a lot of these other languages disappear, and I should get my product key.

26:23
OK, it’s done.

26:25
And I’m going to find product key here.

26:29
Here’s my SLS Product key here. I’m just going to drag that onto there.

26:39
OK, and it should join it up.

26:48
Great.

26:50
OK, so now I’ve got all these guys together, and I got just this Go Region guy kind of floating around right here. So, again, I’m gonna go back to my report Insights.

26:58
I’m going to close out these other drills which I don’t need anymore.

27:03
And this time I want to look at tico reagents, I’m gonna click on go region.

27:20
And the first grade I get is, it’s just going to the market promotional facts, which we saw in, in the Power Query tool, as well as suggested ones that I don’t want to use and not use any promotion stuff. On either side, I can see that I’ve got other tables, other facts, which again, I’m not using, but this go branch dim looks promising.

27:40
And I remember back from my list of reports, I had something, you know, something about branch region, employee quantities of a branch region, so if I take a look at the branch region and go to the branch.

27:54
I can see that I’ve got a relationship already with the Region two branch.

28:02
And I’ve also got employee available. So again, I don’t see any other relationship, and here I’m not using inventory facts, forecasts, recruitment, but I am using employee. So employee is going to be a good candidate here. So I can bring in employee branch code to the gym, go branch, dim branch code and I can join this branch cut to the region. So, again, I go back to Power BI.

28:27
And I’m going to get one more table. I’m going to bring in that missing link.

28:37
A little slow. OK, I think its 100 go.

28:42
Go branch dim. It’s going to check that.

28:46
And I’m going to load it in.

28:49
So we’re gonna have a little bit of snow flaking here.

28:59
It’s taking It’s time.

29:01
Yes.

29:06
OK, it’s up here. So, I’m going to bring this down, a little easier to drag. And I’m going to be joining this two employees, I’ll bring this over.

29:13
So, here is my branch code.

29:16
And, here’s my branch code. So, I’m going to take this and drag it on to branch code.

29:24
Let’s try that again.

29:25
Branch code to branch code should create a relationship for me.

29:32
I said, OK.

29:40
And I now just need to tie region to branch, because I could see region and branch are joined on country code.

29:48
So that.

29:52
And country code is here, and, extract that to that Yeah.

30:08
Analysis is working great.

30:20
Eric.

30:23
Get on there.

30:26
OK, OK, it’s kinda set that up for me.

30:35
And now, I have, basically got the layout and the model that I need.

30:39
So, essentially.

30:43
All these tables are now available to me in Power BI. And I have all the information I need to build out.

30:52
In these five tables. There’s five reports.

30:56
OK, so now I’ll get to the fun stuff.

30:59
We can actually build out our reports.

31:03
So, I’ll go back to the report part, and I’m going to start with one that’s a little bit more complicated. Let’s start with this one. So this is a report. It’s called multi page Editor Port. It’s obviously a port I made. I wanted to kind of add multiple objects and different pages for different queries and filters, things like that.

31:23
I believe I have it. Here it is.

31:25
Here’s what the report looks like. So again, I’ve got a cross tab with some revenue and product lines, and years across top. This is going to be, you know, a similar view of a cross tab.

31:37
You could see in the report, here’s that Cross tab, and here’s my bar chart.

31:47
It’s grabbed that information across to add my chart.

31:51
It’s tiny at both. Those are using the same query, which is query one. Here that information right here, as to where they’re referencing. I’ve got a filter.

32:00
Take a look at page two on that report.

32:08
It’s just a basic list and a pie chart.

32:11
We’ve got Order Method, types and quantities. And then, page three, I believe it’s a map.

32:19
Kind of showing gross profit by country.

32:23
So, again we’ve got a couple of different things going on but I can kind of get all this information.

32:28
Again, if I go in here and edit this as well, just too kind of re emphasize, you know, what’s involved.

32:39
Here’s my queries. I have to kinda go into a lot of these screens, OK, so this is Page one.

32:46
This is using query one. I can figure out, you know, here what that’s using. Click on this. I can see the query.

32:52
If I go to page two, I just kind of check on each object, and figure out what these relationships are, and mapped them out that I can go into my queries.

33:06
Go into each one of these.

33:07
And again, product line doesn’t touch, It’s a double click into it, OK, here’s where it’s going to, sales query, products, product line, that is coming from.

33:20
Products that kinda go to this whole thing again, there’s Products.

33:26
A table called Product.

33:38
I think it’s a separate product lookups here.

33:44
So yeah, it’s a bunch of different tables that are all kind of joined together. Intermediate layers.

33:50
Again, making it simple, I have to kind of go in and capture all this. Again, here’s the filter. Double click it to get the information. All this information is going to be spelled out for me.

34:02
Nice and easy, so I can just kinda print this out and say, OK, but this report, here’s everything I need. I’ve got my model already built from the prior step.

34:09
Here’s what I need to build. So let’s go ahead and build that out now, just kinda quickly show.

34:15
So I start with H one.

34:16
I’m going to first do a cross tabs, called a Matrix, or a Table, in Power BI.

34:26
Here are my fields, again, I didn’t get to the metadata cleanup, but I’m going to just kind of pull it in, so I’ve got Product line, which is under my product line.

34:36
And I’m going to grab the English. And that’s going to be my.

34:43
I rose, drop that in there.

34:47
I’ve got the year from the time dimension, it’s kind of my time.

34:56
Here, I’ll put that into the columns, and then it wants revenue for, sort of the, the Intersection Point.

35:05
That’s going to come from my sales facts.

35:09
So, I’m looking, I see gross margin, gross profit, quantity, sales total. I don’t see anything in here for revenue, so I need to know more about what is actually what revenue is.

35:20
Again, I can click on this, and it’s gonna give me, you know, the full lineage of where that’s coming from. It’s going to show all the different layers that I would have to kind of navigate through and Ferric Manager, so here’s my query. It’s the sales query sales, fact revenue, which is in another namespace, called Sales Query, which is in the business view, under sales and marketing data.

35:43
And I can go and get down to this, this level down here, and it’s just going from SOS, Sales Fact, which we’ve got, and it’s actually sale total sale total is revenue.

35:52
So, it makes it very quick and easy for me to figure out, OK, I just need the sales total, so drag that into my values, make this a little bigger so we can read it.

36:10
OK, so here are my numbers. Let’s take a look at the original reports.

36:22
Go back to page one.

36:29
And let’s compare.

36:33
And I could see, my numbers look pretty inflated, camping equipment is showing 332 million versus 60 million for 2010.

36:42
So why is that?

36:44
Can I check my report insights?

36:47
Up as a filter here? I forgot about this filter. I’m looking at retailer, Country equals United States. So let’s go ahead and quickly apply that.

36:55
So I’m going to go into my filters.

36:57
I’m going to grab my data here, and I’ve got my region.

37:05
And I want the country.

37:10
There’s a region retailer country as a country and with the English spelling of it.

37:18
I’m gonna put a filter on this page, and scroll down to the bottom, and let’s set it to United States.

37:27
Numbers are going to refresh.

37:36
And now they look. Exactly correct.

37:38
So I’ll go back to my port.

37:43
And here’s my 60 million, 1, 4, 3, 4, 9, 8, oh, eight matching. Exactly.

37:50
So know that kind of quickly build this is cross tab.

37:54
And I can do the same thing very quickly.

37:56
I’ll just add in a Cluster chart, and we’ll put in the same fields, essentially.

38:05
Want my XS X access to be the product line?

38:14
Collection is going to be the years.

38:21
And the values would be that sales total, I believe. Cell total?

38:30
Yes.

38:37
And that’s pretty good as well I can see, for camping equipment.

38:41
It’s going up, and then drops off, same thing here, I can mouse over and get the numbers, which should match again with essentially the same data as we see here.

38:53
So it all looks pretty good. This is actually sorted a little bit differently, it’s got personal accessories at the end, which I can just sorting here. But I’m not going to worry too much about cosmetic things right now. But essentially, you know, I’ve got the, the first tab with this, built in just a couple seconds here. So, I’m gonna go to page two.

39:10
Let’s jump back to my insights. And I’ve got a list and a pie chart, so I can drag them in, and I’m going to use order method, type, and quantity.

39:22
So, let’s do a list.

39:29
And I want what our message, we haven’t used yet.

39:33
Got all these languages, it makes it a little ugly to go through.

39:40
And let’s grab quantity this time.

39:49
And then I’m gonna create a pie chart.

39:54
It’s the same thing.

40:01
Quantity and Order Method.

40:18
Let’s see, how actually change the way it’s doing the splices here.

40:30
Just do.

40:35
Yeah.

40:39
Screen size here. But hopefully, you can see it.

40:43
If I go back to the manged, to look at the numbers, we should be trying out.

40:52
So the 6, 4, 2, 6, 5, 9, looks good on the e-mails. The pie is sort of inverted. But again, the slices are all lining up web being the biggest one. Different colours.

41:04
Obviously, they’ve got different themes that are not going to initially translate over exactly what, you know, as far as the actual look and feel, it’s pretty simple and the numbers are trying out, which is going to be important step here. So, I’m gonna do one more page, and then I’m going to kind of show the full the full thing which I did ahead of time.

41:23
The last page was a map. So I’m gonna go and just added a new visualization page down that run, and then I’m gonna go back to my insights. And I can see I’ve got a map on here, excusing Query seven.

41:36
Here’s Cleary seven and again, pretty basic just retailer, count, country, and gross profit.

41:42
No filters.

41:45
So, let’s just build this out really quick.

41:48
Where is my map strip that on there? And I want my location to be the region.

41:59
Taylor Country.

42:05
Tree, key, and location.

42:15
And let’s grab my Sales. I did gross profit.

42:21
Right here.

42:37
OK, and then I would want to I can adjust the ranges here again.

42:44
Back to.

42:48
Here. This is pretty hard to read. I probably made it bigger.

42:51
Back at Brazil, you know, 56 million plus profit.

43:01
Upset with the wrongs number on there.

43:19
I was prompted.

43:21
Oops.

43:24
Yeah, OK, come on.

43:30
Region.

43:44
Yes.

43:48
It’s not a region.

44:04
Something wrong.

44:23
It’s showing negative for some reason.

44:36
This one?

44:41
So, that’s why I’ve actually built out all the same ones. But there were other reports in the,

44:48
Insights here. So, this was just a single page with three Sync report with three pages.

44:53
I’ve also got this retailer, type our product line, gross profit. Again, just a single cross tab, report.

45:04
Top level, org chart.

45:07
And these are all sort of data here.

45:12
We just built.

45:17
So, this is the first one, Employee Quantity Sold by Branch Region.

45:48
Sorry, guys. I can hammers.

45:54
Yes?

45:58
Four ports, there we go.

46:02
Yes, So here is the Report.

46:09
The other one.

46:21
So scan ship gripping by regions. OK, you know, some abbreviated numbers here but essentially 4.6, 5, 5.3, the numbers to hang out here that I’ve got my sales staff, buy the region tight out here. So.

46:38
I was able to kinda with the model, and they got these reports, you know, I think we’ve got that three pages and a model with the slide deck today, and, you know, already 45 minute mark. So you can see, hopefully, sorry about that one that’ll pick up there. But essentially, you know, it’s, it’s very easy to kind of get all the information out at the fragment manager models in the reports into it. Like a clean, concise summarized view, which makes it very easy with even very little experience in another tool, a Power BI or Tableau. They just check a couple of boxes. These are the tables I need if you have intermediate tables that are kind of join, that aren’t obvious, how to identify what those tables are and bring them in, make sure you have the relationships, the filters, expressions and calculations and then you can basically PDF this.

47:33
Oops, wrong one.

47:34
This one.

47:36
This is just a couple of pages in this report.

47:38
This can be, you know, output any cargo supported output format. Get us to one of your developers and say, hey, here’s everything, any, go build out, you know, all the General Ledger reports are all the sales report, whatever it is you need.

47:55
And it’s just sort of initial where we sort of, purpose this tool. Again, if we remember for the beginning you know, I ran this for the Go Data Warehouse query. But, again I could run this for any package we have.

48:07
If I wanted to just see, you know, audit, which isn’t going to be a very useful thing to rebuild, but it’s gonna go in just dynamically grab all the reports it found that are tied to the audit package, and give me the raw tables of what they’re using.

48:25
As far as they relate to the different, you know from our advantage, your presentation layers, and then a wrong, here’s a list of just the essential tables you need.

48:33
I can just bring these and then go through each speck and say. OK, It’d go to Lists report, It’s using this called Execute Reports. Here are the fields I need here, the filters.

48:43
Just makes it, in my opinion, just having to do it the old way for many years. I find this to be a big time saver. So, hopefully that was clear in the demo.

48:52
I’m going to jump back over to the deck and turn it back over to Steve for a few, and then we’ll take some Q&A at the end. So, thank you.

49:04
All right, thank you Todd for the demo presentation. Before we go into Q&A, I just wanted to let everybody know, we are offering a Migration Kickstart Engagement now, for anybody who’s interested in exploring the use of the Report Insights tool for your own migrations. And Migration Kickstart is a collaborative engagement that starts at two days, excuse me.

49:29
The kick-start essentially involves us pairing up with one of your staff members to leverage the report insights data against all of the reports within a single Cognos package. And we help out with report creation on the target platform based on that output, similar to what Todd showed in his demo.

49:50
Package, includes setup of the report insights, software, and consulting and mentoring a system from our staff.

50:01
So, a little bit more about Senturus, we are the authority in business intelligence and its interests. We concentrate our expertise purely on business intelligence with the real depth of knowledge across the entire BI stuck and across multiple platforms, particularly Cognos, Power BI and Tableau.

50:24
Our clients know us for providing clarity. Through the chaos of complex business requirements, disparate data sources, and constantly moving targets, we’ve made a name for ourselves because of our strength at bridging the gap between IT and the business. We have a lot of years of experience and a lot of client projects behind us. As I mentioned earlier, a great deal of our work over these years has been very, very focused on migrations.

50:51
We deliver solutions that give you access to reliable analysis, ready data across your organization to help you quickly and easily get answers at the point of impact.

51:03
We offer a full spectrum of BI services.

51:06
Everything from dashboards, reporting, and visualizations, all the way through Data prep, data warehousing, software to enable and accelerate migration, similar to what you’ve seen with report insights, training and mentoring options. We really can be a one stop shop for your BI needs. So, we do encourage you to reach out to us, for anything you do need. We’re so confident in our team and our methodologies, but we do back our projects with a 100% money back guarantee, which is unique in the industry.

51:40
In addition to report insights, I wanted to just briefly touch on our Analytics connector, which might be of interest to some of you. The Analytics Connector is a software product that lets Power BI and Tableau tap directly into the data in Cognos environment that effectively provides an interface directly to your Cognos packages from Power BI and Tableau. It’s perfect for bimodal and transitional environments as a way to give you quick access to your Cognos model. The data from Power BI, if you’d like to get more information about the Analytics Connector, you can check out that link on the slide here. You can also find it just by doing senturus.com, and I’m looking at are clicking on our product menu.

52:32
We have a long, strong history of success here: 19 plus years, 1300 plus, 2500 plus projects, and we are ready to help you when needed.

52:45
You can expand your knowledge, as I said earlier, by visiting our resource page on the Senturus website. On that page, you’ll find all of the details from today’s presentation. You’ll also find past presentations related to the Analytics connector and a multitude of other useful tidbits. So I encourage you to browse around in our resource library to get additional info.

53:13
We do have another upcoming webinar here, later this month. We’ll be doing a session on accelerating the deployment of Power BI Datasets, and reports, will cover the new Power BI Deployment Pipeline feature.

53:27
So, if you’re interested in the deployment pipelines and you’re working on a migration or actively using Power BI in your organization today, I would encourage you to join us on August 27th.

53:42
We also offer a complete set of BI training options on all three of the platforms that I mentioned earlier, Cognos Tableau, Power BI. We are an ideal choice for organizations running multiple platforms, or those of you who are moving from one platform to another. And, we provide training and multiple modes, and we can mix and match to suit your user base. So, you have options for instructor led online training, and also private training. We also offer some computer based training options. So, again, visit the Senturus’s website for more information.

54:21
And for any additional resources, you know, the Senturus.com, we’re committed to sharing our expertise with the user community. So do look around there, because you’ll find a great deal of valuable. And so, with that, let’s launch into the questions and answers, And Todd has been looking over some of the questions you submitted.

54:43
So I’ll let him take it from here. And I may jump in, also, periodically, as we’re going through. Q&A. Thanks to say. A couple questions.

54:51
I got, try to get to here before our time. One question I saw was, what does it to show the calyx logic columns, including complex if else, statements? And the answer is, yes. Let me jump back.

55:05
I believe some of these audit reports have it. Let me check, Yes, so here’s one, this is the agent execution history by user.

55:15
You can see this is sort of a more complex than nested statements. So it’s going to kinda give you all this logic right here.

55:23
Um, got a couple of different, you know, if then statements it’s got, you know, substring. So it’s gonna capture it exactly as it’s written in Cognos and hopefully keeps the formatting as well. So, it’s going to be easier to read. Again, I’ve got multiple expressions over here and filters.

55:39
So, again, this, this specific report, probably, not one that you’re going to recreate, especially since, you know, the Cognos audits aren’t going to necessarily apply in Power BI. But, you could technically report off the database, if you wanted, but you can’t get this level of information in the expression is available. And I can also use a lineage to the drill down to figure out, you know what this is coming from? I got Melissa tables, what, I need, a folder, so, it’d be very easy to kind of quickly build something in this fashion from the Insights tool.

56:11
So, what else we got?

56:14
Um, how easy is it to set up the Cognos insights? Is there a best practice? Determine which reports present themselves well to be migrated to their system, like, Tableau? That guy had Alicia’s quite as well.

56:28
As far as, you know, the ranking things based on relationships, that’s not something we have. We have done a demo. If you go to our knowledge base, there was a demo on sort of a more summarized view of this topic using the insights as well. It can provide things that, take advantage of, you know, both audits.

56:47
You know, hundred times there’s report, Ron, who’s running it, as well as, it’s sort of common tables and queries and things like that. So, you know, it can get a bubble up, things like, Hey, a lot of people are going after, you know, the revenue numbers. I buy, you know, product line or things like that, so, can help you identify, if you don’t know, you know, what?

57:09
What the target for your migration and, sort of, you know, break up the list. I’m assuming, if you’re a longtime Cognos customer, you’ve got hundreds, if not thousands and thousands of reports, you know, focused on which are the ones that we need to migrate over? First, if you are planning to migrate off of Cognos? So, does the summarize type tools that are going to leverage audit and more of, you know, analytics on summarized level kind of help figure out the why. And this is going to help you figure out. You know, the how and the actual details as to, OK, let’s actually build this now. We know we need to move. Let’s get it done. Let’s target this, this package, here the reports, the tables that we need, the specs. I can kind of quickly take these and go ahead and enough to my developers or myself, and build them out very quickly. So, that’s sort of the, the approach should sort of a multi-tiered approach.

58:00
But, again, I think this can also ties into another question, No.

58:04
Is this fully automated? You know, and the answer is, no. We looked at a bunch of different tools, different code techniques. And, just, unfortunately, due to the way and the complexity of the Cognos query engine, there’s no really clear way to directly kind of click a button and migrate reports over for Cognos to Power BI or Tableau. There’s going to be some manual effort involved, especially dealing with sort of intermediate tables, like I had to bring in, like, the product, in the region, to kind of fill in those gaps. Those things get actually evaluated, you know, at runtime, with the query engine.

58:42
So because of that, it makes it very difficult to build any type of tool. It’s going to successfully kind of convert these over.

58:48
So this is sort of, again, not just a single button, but it is pretty easy, like, I showed you know, to build, and to model your data with this tool.

58:58
So, again, that’s something I’m aware that there anywhere in the market, and we tried to create this tool ourselves in this, kind of, just running into roadblocks here and there.

59:10
So this is sort of the way that we had to kind of develop that, and I think it works pretty well.

59:17
Let’s see, we’ve got time for maybe 1 or 2 more.

59:22
Todd, actually one question that came up earlier in the session. I have some thoughts on it that you may want to take it first. Is this question about, would we recommend bringing tables, and depending on what the report needs, as opposed to trying to build a more generic dataset, the multiple reports could use? Do you have any thoughts on that?

59:42
Um, yes, something like can we built this specific report that I demoed today to kind of target a single package and then to kind of strip it down to the bare essentials in order to kind of eliminate any extra noise and make it very fast to just replicate.

1:00:03
A lot of the logic that was put in this report could be disabled, so that you could say, hey, show me the full stack of the entire FM model.

1:00:14
Um, regardless of what reports are using watts. So, I can get the entire model, sort of, documented this same way and say, hey, here’s all the tables here. What the namespace is tied to, regardless of, if the reports are actually using them? Or not? So, if you wanted to kinda build a full model out in another tool, it could do that as well. But I was, you know, it’s kinda focused on, you know, trying to, you know, just get it done and get it out of Cognos is an assumption that, you know, we’re no longer going to use Cognos. We need to get these 10, 15 essential reports out of cargo so that we can shut it down, and you can put the move to this new tool. So as, far as your question, Yeah, we could definitely use this information that we have got, you know, all the reports back to the entire model catalog, and they can kind of be repurposed in many different ways. So this is, again, a brand new thing that we’re kind of rolling out.

1:01:10
But depending on your needs, we can now use the custom fitted to do different things. So, if you have a different vision, or your approach is different than kind of the way we laid out, definitely reach out, and we can kind of figure out, you know, if there’s another way that we can present this data, to make it, you know, do exactly what you need to do to help with your migration.

1:01:30
Thanks, Todd, And you know, one other thing I would add is that one of the key benefits of Report Insights is, it actually allows you to take a bit more of an agile approach to your report migrations. So, because, report insights, your output for a particular report gives you the, basically, the minimal set of tables that are required for the migration, that allows you to do a quicker conversion of the report. And as you iterate through multiple reports for your target environment, you can always re-use the work that you did with earlier reports. And just add the additional required tables as you go along.

1:02:04
So it allows you to do little report migration sprints, rather than the more traditional approach of trying to migrate the entire model first, and then, and then pulling the reports, and after that, we are past the top of the hour, so I think we should wrap up, but thank you, Todd, and thank you, everybody, for attending. If we didn’t get to your question, we will take care of that after the session, and we’ll get the answers out to you. So, thank you again, everybody, and have a great rest of the day.

1:02:36
Thanks, Todd.

Read more