Streamline Cognos Migrations & Consolidations

×

Michael Weinhauer
Director
Senturus, Inc.

In addition to BI consulting, Michael’s team created the Senturus Analytics Connector, which lets Tableau and Power BI use Cognos as a data source. Michael has been designing, delivering and selling analytics solutions for over 20 years. He comes to us from Oracle, IBM and SAP, where he spent over 20 years in different roles acquiring a wealth of hands-on, practical BI and Big Data experience.

 

Read more

×

Greetings everyone and welcome to the latest installment of the Senturus Knowledge series. Today, we’re going to be discussing the topic of how you can streamline your Cognos migrations and consolidations.

0:21
Before we get into the presentation, we always have some housekeeping items. We always get asked.

0:28
Can I get a copy of the presentation? And the answer is an unqualified. Yes.

0:32
It is available on Senturus.com in our newly revamped website, under the Resources tab and the renamed Knowledge Center, or you can click on the link that should be provided in the chat window shortly.

0:50
Today’s agenda, we’ll do some quick introductions of our speakers, and we’ll get right into the topic of the migration challenge.

1:00
And, we’ll do an overview of the Migration Assistant for Cognos discuss common scenarios and use cases of the Migration Assistant.

1:09
And we’ll follow that up with a demonstration and stick around through some real quick, great, free additional resources.

1:17
Learn a little bit about Senturus and stay for the always great Q&A at the end. With regards to the Q&A, we have the microphones muted on everybody out of courtesy to our presenters.

1:32
However, we encourage you to enter questions via the GoToWebinar control panel, so go ahead and enter questions in there and we will try our best to answer them during the webinar.

1:42
If, for some reason, we are unable to, we will post a written response document along with the deck today, and a recording of the webinar on, again, Senturus.com in the Knowledge Center.

1:58
So, today, I’m pleased to be joined by my colleagues, Todd Shuman, and Song Gao.

2:06
Todd has over 19 years of business analytics experience across multiple industries. He also regularly connects with the IBM product development team.

2:14
He’s been at the forefront of Cognos Analytics upgrade and installation process since it debuted. He lives with his wife and two daughters outside of Washington, DC in Virginia.

2:23
And Song is one of our developers and is responsible for not only developing the Migration Assistant but also, the Senturus Analytics Connector, a connector that allows Cognos data to be leveraged via Tableau and Power BI for data visualization purposes.

2:39
And, the Senturus Migration Assistant Utility that exposes Cognos report metadata in a series of additional reports to speed BI system migrations, which is what we’re discussing today.

2:50
My name is Mike Weinhauer, I’m a director here and Senturus, among my various roles, I host and sometimes present as in today on various topics related to business analytics.

3:03
So, thank you, all for joining us here today.

3:06
We always ask some questions about our audience and what’s going on in their organization as it pertains to the topic? So, I’m going to launch our first poll here and ask you all to respond, this is a multiple choice poll. What are your goals for Cognos? Are you changing an underlying data source?

3:24
Are you moving to the cloud? Are you migrating to a different by BI platform? Are you looking to just clean up your Cognos environment? or, something else?

3:35
So, go ahead and put those answers in.

3:41
I’ll give people a few seconds to do that, so you can all participate.

3:52
Quiet audience here today. There we go. There’s some more people voting.

4:00
See, if we can get to 75%. All right, or at 75%? I’ll close it and share it back.

4:06
So, you should be seeing that now.

4:08
So, about a quarter changing data sources, closer to a third, moving to the cloud, little over 20% migrating to a new platform, majority cleaning up the Cognos environment. That’s very interesting.

4:20
And then, 20% is other interested in wondering what that other is, OK.

4:26
And then, our second poll here is, How many Cognos reports does your organization have?This as a more straightforward one?

4:37
We are missing one of the entries there, but you get the idea. Is it less than 500, between 500,000 to 11,000, or more than 10000.

4:48
Or if you don’t know, you can say you don’t know.

4:54
So, it should be a little quicker.

5:05
Right. I’m going to go ahead and close this out and share it back with you all.

5:09
So, about 20% on the small side, 15% on the, medium sized, and then most of you are in that thousand to 10000 range and then, another 10% with over 10000, she that’s a pretty good range of different sizes of Cognos environments there.

5:35
So, thanks for sharing your input there. Let’s get into the core of the presentation here. We have quite a bit to cover.

5:43
So, the migration challenge. It’s a bit of a redundant phrase because migrations are sort of inherently challenging.

5:52
They bring the promise of better performance, and the ability to harness new functionality that will enable it.

5:58
Ideally, enable better, faster analytics, and drive better business outcomes, enable self-service analytics and things like that.

6:05
But it comes with apparel of risk, and complexity, and time, and cost.

6:12
So, they can be very daunting and they come in many flavors which we’ll discuss going through here.

6:24
The complexity is hidden so when it’s properly built, the data flows through from cryptic source systems and massive data warehouses with thousands, tens of thousands of tables through complex ETL logic and transformations into elaborate data models, and then out into complex reports and dashboards that are, you know, ideally easily consumed by the end user.

6:53
However, the bad news is, it’s still there, and even worse, for all of you who work in the trenches you know that.

7:03
How many times has someone come to you with a report and they say, Oh, look at that. Can you build this report or move this report? It should be really straightforward, It looks really easy.

7:11
Like, it’s an Excel spreadsheet and you look at it and you’re like, yeah, I know what’s behind that thing, and it’s going to be a huge mess.

7:18
So, that complexity is all there and then, of course, the more complex that environment is, the trickier the migration is, right.

7:28
So there’s very complex Cognos content You have tens, several tens hundreds of models and packages and lots of data sources. Again, maybe thousands of reports.

7:43
It’s multi-layered and duplicate an overlapping in terms of the reports and packages.

7:48
Models have proliferated and grown over the years. And, you know, framework matters, it’s a 20 year old plus tool and, you know, likely you’ve had some turnover in the organization from the first time those models were built.

8:04
Here, it creates more complexity, and it gets messy, right?

8:08
You have lots of ports, in my folders, got duplicate unused, and overlapping reports.

8:14
There’s all kinds of challenges, here.

8:17
Not to mention businesses merging and realigning, so you’ve got M&A activity and then you have platform and data source migration, for example, moving things to cloud.

8:28
So, there’s the need for migrations for various reasons and lift and shift, just taking what you have and trying to plop it into a new environment and expecting it to be better Simply doesn’t work, right? When you move homes, do you just pack everything into the truck and take it to the new house?

8:50
Maybe, but probably not, right? You have a garage sale. You bring a bunch of stuff to goodwill, you.

8:54
Those are some things away, and you clean things up, right? It’s the perfect opportunity to do that.

8:59
A failure to rationalize and go through that exercise leads to bloated migrations, suboptimal accessibility, and performance unhappy users and lower adoption.

9:14
A big part of this is unravelling the Cognos content, right?

9:18
That all that complexity and that that kind of deadweight that’s in there.

9:22
So to scope and complete your consolidation, your migration and project, you have to sort of unravel all that stuff, the models, the reports, and the usage, and will demonstrate how painful that is in terms of time and inaccuracy and just access.

9:37
The Cognos audit is designed to answer some of these questions, right? about what’s being used and stuff like that, but, it’s only at the report level.

9:48
And it doesn’t really break down the reports into all those objects that you would need, or the model, or package level.

9:56
And, it’s limited, if you can’t tell you which reports are never run, or can you view the data in the My Content.

10:03
Right. So, you don’t have, and you don’t have a consolidated view of the data sources and models that are needed and used.

10:10
And there’s a lot of challenges around that, and this can be very time consuming, and it leaves you, kind of with this.

10:15
Know, this poor frazzled woman who’s like, Where do I even know? Where do I even start with this? Otherwise, scope, It isn’t even worth it.

10:23
And, how much is this going to cost versus doing just renewing the Cognos license, right?

10:29
So, you may decide that the cost outweighs the benefit, and they’ll go my vacation plans, right?

10:35
You’re going to be working nights and weekends, because analytics is the backbone of so many organizations that you rely on these things. So, you can’t, a, failure is not an option.

10:45
And downtime isn’t even really an option, right.

10:48
So, there’s going to be up time that we’re going to be running both environments, till you can cut over.

10:53
And that’s costly and risky.

10:55
You know, all the things that go with that.

10:57
So, all the aforementioned challenges here are what inspired us to create a powerful tool that helps to significantly streamline the migration process.

11:12
Now, it’s important to have a methodology to approach this as well and Senturus we’ve been doing migrations in their various flavours since we started doing this over 20 years ago.

11:27
And the methodologies is critically important.

11:31
Better, the upfront effort to assess, create a roadmap, and optimize the migration, the greater your chances of executing and doing the last steps successfully on time and on budget, what and whatnot.

11:47
really, they go up as Frank Lloyd. Frank Lloyd Wright famously said, it’s easier to make changes in pencil than with a wrecking ball. In other words.

11:56
You don’t do your work up here.

11:58
Once you’ve gone and done this, you might be looking at a lot of rework, right? Which, of course, increases risk, cost and time.

12:05
So, the Migration assistant helps you automate what can be an extremely slow labour intensive and manual process.

12:14
So, how does it work?

12:16
Well, it helps to eliminate hundreds of manual clicks to discover data fields, filters, calculations, all the stuff that you need to move.

12:25
And it helps it programmatically decodes in inventories.

12:30
Your multi-layered Cognos reports and packages types that into a database.

12:34
And then surface is some prebuilt content for you.

12:39
And not to mentioned, you can create ad hoc queries depending on your, your needs, to that database, to identify all these deep, all these details about your environment.

12:48
And we’ll show you all this stuff in the, in the tool in a little bit.

12:55
So, how it works is, it really helps with that upfront process, where you, whereby you can eliminate unused, and duplicated or broken items.

13:07
Identify those you can plan, and prioritize your higher priority or impacted items.

13:13
So, do those first.

13:15
Simply simplify and consolidate similar items. So, again, you know, understanding what the capabilities of Cognos are, versus the new tool you might be moving to.

13:27
You, you might be able to replace, you know, like, 5000 reports with 500 reports that are have hierarchies, and drill through, and prompts, and whatnot.

13:38
So, that’s a big part of that exercise. And, automate a lot of that manual work, and eliminate the errors that go in, that come along with manually pulling all that information, and putting it in spreadsheets and whatnot.

13:52
And then, what we do is, is actually create a report, and dashed dashboard, recipe’s, if you will.

13:58
So, we give you the cookbook, and it says, here’s what this report is made of, and here’s how you, here’s what you need in one place to go and rebuild that somewhere else.

14:08
And it reduces the need for what we call, you know, unicorns, these kind of mythical people that understand both the source and target platforms, which is non-trivial, right? They understand the architecture of the source and target, the different tools. The nomenclatures, again, what are the capabilities of the new tool versus the old?

14:29
No. Can you even do it, right?

14:31
There are things that, you know, if you’re if you’re, say, moving to Tableau and you have invoices, No. Good luck doing that in Tableau. Right? It’s not made for that. So, you have to consider all that sort of thing.

14:44
Those unicorns are people that, you know, we have several of them here at Senturus that we call them multilingual, that they understand Cognos and Power BI and Tableau and can do that.

14:54
But you want to use them and kind of sprinkle them through the project, and have people that don’t have to be those unicorns be able to execute on your project.

15:07
So, again, how it works here is that it extracts the Cognos metadata into all its component pieces. So, reports.

15:16
All the pieces pages, lists, columns, query items, filters, the data models breaks down the namespace as a query subject, query items, and relationships, and it pulls in the lineage between the reports and the models and the database.

15:30
So all the pieces that you need to migrate, whatever it is you’re going to migrate.

15:36
And it pulls the report execution history.

15:38
So it sort of marries the audit information along with all this metadata and pipes, all of that into a SQL server database.

15:46
And we provide a Power BI workbook that you can use that I’ll show you in just a little bit.

15:53
And we also have a set of Cognos content, as well, that basically reports on the on the metadata.

16:00
And you can do ad hoc queries, because, again, it’s of SQL server relational database.

16:06
So, now, let’s talk about a few scenarios, where different migration scenarios, where you might use the migration assistant, where it might help you, and we’ll discuss three of those.

16:18
The first one is a data source change, and the migration assistant here can help you accurately scope, budget, resource, and manage those data source migrations.

16:31
Now, this is where you’re moving from one data source to another. Maybe you have a new data warehouse, maybe you’re moving from on prem to Cloud.

16:39
And what it does is it helps you, it identifies which reports and models rely on that legacy data source, combines the inventory and usage statistics, So you know, here’s some stuff I can leave behind. Here’s the stuff I really need to move over.

16:51
It lists all the tables and columns used.

16:54
It allows you to search for specific keywords, inquiry’s and filters, for doing things like compatibility, checking of functions, and helps you to identify and really, you know, plan out the migration process.

17:06
So, I know, Todd, if you can talk a little bit about a large financial institution that we worked with, where they move database’s a little bit.

17:17
Yeah, definitely, so, this has been coming up more and more often on our radar. You know, customers who are looking to move, you know, stay on Cognos, but move data sources from on prem to cloud or just various other. Moving from vendor to vendor.

17:31
Um, and what the tool does is it’s able to identify, you know, what reports are targeting what data sources. So this Civic Klein had, I think, about 17,000 reports and hundreds of different FM models.

17:46
And there was combinations of Teradata, which was the source that they wanted to move off of on premise Teradata.

17:52
It was Teradata.

17:53
They also had Teradata, blended with Oracle and other various database tools that we kind of labeled as Hybrid. And then we had non database stores. So, the first thing to do is, you know, show me all the reports that are hitting Teradata sources, and its flag those on. Top of that, we can get down to table level. So each report we can see it’s hitting Teradata, and it’s going after the specific schemas, and cables, and even columns. You have that kind of lineage to get down to that level of detail.

18:22
And we also wanted to target the audit data, so you only want to bring over reports and migrate reports that have been run in the last six months.

18:28
So, by running that inventory and putting everything into this migration assistant database, seeking that up with audit and then creating custom reports to kind of target the reports, we wanted to be able to kind of get this large output that was giving us the tables, the usage, everything we needed.

18:46
Once we kind of had that, we were able to then sync it up with a migration schedule that was being provided by the team that was moving from Teradata to that Google big query Cloud database.

18:56
And they were doing migrations in groups of tables. So, a couple of hundred, you know, a week, something like that.

19:03
So, we could see which reports for candidates to be moved as they were being completed.

19:07
So, it really kind of worked out really well with being able to kind of capture all those different pieces, getting down to the level of detail we needed, and then being able to move them in chunks to kind of sync up with the migration team.

19:21
The other challenge that we had to do with this project was there are lots of different database specific functions, especially the date functions and ones that we identified as incompatible with, you know, the new platform.

19:33
So, for example, like, there’s a common like to date function that converts, you know, a date from one format to another. That’s syntax was completely different in the GCP database.

19:42
So, we wanted to find about 10 to 15 different database functions, scattered through and the 17,000 reports.

19:50
It would take you probably years to manually go through each report manually and find that.

19:54
We just put in, you know, search strings, scan through every expression in the entire content store, and we found every report that was using that function.

20:02
We could then also find out who the owner and the developer was, and then send out e-mails to them, and ask them to switch, you know.

20:08
Function X with function Y, and then make sure that was done. And then that way, once we convert the reports over, we’re going to get errors when we try to run it against new database.

20:16
So lots of different ways to kind of work with these data source changes that are becoming more and more common today, and I think this tool just saves a ton of time and effort, as far as trying to accomplish those goals.

20:29
Alright. Thanks, Todd. That’s very helpful.

20:31
So, again, know that the migration assistant combines inventory and usage statistics, enabling you to create a prioritized and efficient migration plan.

20:41
And minimizes it.

20:42
Keeps that project scope, nice and tight, by focusing on your high priority database objects, eliminating unused objects, and easily tracking down, you know, those differences between, you know, function capabilities and all that stuff in the, in the database.

20:56
So, the second use case we’ll discuss here is the one, you know, people most commonly think about it and that’s actually migrating analytics platforms.

21:06
Now, you might be, that could be migrating from the whole thing from one tool to the other, right, you know, from Cognos to Power BI or Cognos, too.

21:16
Tableau, or, Or, vice versa, or whatever that is where you might be, and what we see more commonly now is migrating a portion of that environment or a department or a subject area, right? Instead of whole hog, you kind of partition it out too.

21:32
Spread that out, and, you know, not eat the whole elephant in one meal And the other one is, you might be migrating on to this on the same tool, but between major versions, right?

21:44
So, in a lot of cases, you know, when they really kind of do are architecture and a redesign, you don’t just unfortunately run the software and upgrade it. You know.

21:51
It’s, uh, in order to take advantage of new things there’s incompatibilities, there’s and you want to go through that rationalization process, so it’s really more of a migration than an upgrade percent.

22:02
And, in these instances, the migration assistant, again, helps by identifying the data sources, inventory, and the report design components, And it gives you that recipe book of how to, what, what your models and packages and reports are comprised of, and gives you that recipe book for how to rebuild that. The data items, the calculations, the filters, the SQL, whatever that is, also helps you to prioritize identifying the most used reports, and show, the rarely used an unused reports that you can, you know, call or de prioritize it at a minimum.

22:39
Now, Todd, I think you have some experience here in a little comment on that as well.

22:44
Yeah, definitely. So, this is sort of, when we are, at least envision this tool, the main use case that we did, that, I was thinking of, you know, as a long time cognitive developer who has been taken up, you know, Power BI and Tableau.

22:57
I’ve been asked to recreate things and other platforms, And it’s just been extremely time consuming to kind of have to capture all the information from a single report, just the amount of clicks you know, navigating from the pages, you know, the objects, the queries, that query games that filters just getting the report itself is talking to him. Then, you going to go back check at the FM model, which has, you know, multiple layers. And it’s just lots of complexity, tables, joins, things like that.

23:23
So, being able to kind of get this sort of single output page, where you can just kind of click and get everything you need on a single screen, it’s just such a time saving value, in my opinion, that I find it, you know?

23:37
Just saves.

23:38
If somebody is probably something, especially when you’re looking at hundreds of thousands reports, I just don’t see how it could be done, without just drilling dozens of people and hundreds and thousands of hours at these projects. So, you’re going to show a demo of this little bit. I’ve got a webinar I did last year, and I think, Mike, you’ve got a blog post. You’re working on that ship. Yes, shortly as well. That kind of just shows, you know, the, we call the old method of manually capturing these reports and the specs, versus, you know how much easier it is to use this tool. So we’ll definitely get a chance to show you that.

24:10
Yes, thank you, Todd.

24:12
And then the sort of third use case here is, is that optimizing your cognitive footprint. So, I guess the polls were a little bit of foreshadowing there, in terms of what we’re going to talk about.

24:22
But, if you’re just looking to, you know, put your Cognos environment on a diet and optimize that cost of ownership.

24:30
The Migration Assistant helps you to identify, rarely used unused reports, and the reports that are used the most by the most users identify underused user licenses. So you’ve got people that aren’t using the system that you’re paying for licenses for. You can optimize that.

24:46
You can shrink packages safely by removing on referenced areas.

24:50
Right kind of callbacks, some of that stuff that that might make your reporting environment less desirable and cumbersome for those users when you’re really trying to enable self-service self-service.

25:02
And in this case, there are complimentary tools here which many of you likely own.

25:08
The difference here is that we provide the insights, but implementing the changes requires those complimentary tools or, and, or consulting services.

25:17
The Migration Assistant really just provides the deeper insights into the Cognos audit data and combines the two.

25:23
So, the result there is, Is happier users lower costs, and increased adoption, because your environment is more accessible and easy to use, because you’ve given it the renovation.

25:37
Todd, any comments on that?

25:40
Yeah. It’s going to just save so much time to be able to get this information out.

25:45
It’s, you know, without having the access and visibility into this level of detail, it’s just, with the way Congress is designed, It just doesn’t, it’s an easy to get that information.

25:56
So, you’ll see, once you see the demo, that, it just such a different way to look at your data than you’ve ever had access to in the past, Just very valuable, in my opinion. Great. Thank you very much. All right.

26:09
So, then, without further ado, let’s, let’s do demo here.

26:15
So, what I’m going to do first then, is kind of show you what the kind of the, the old way was.

26:24
And so, in this demo, what we have is, I’m in my Cognos environment here.

26:30
And I have a report that is called Tata Report.

26:33
And I’m just going to run that report, just show you what it looks like, Right?

26:37
And we created it for purposes of this demonstration to show that, right, you have a It’s I don’t know, I call this maybe a fairly simple report, but it has some complexity to, It’s got three pages.

26:50
Got a cross tab with my product lines and years in revenue.

26:54
And a bar chart, right, a vertically stacked bar chart with, again, revenue.

26:59
And you’re in the same information expressed visually in a bar chart. Second page shows my quantity by order method type and a simple list, and a pie chart, kind of breaking that stuff down, so you can see its contribution to the whole.

27:11
And then, the last one is a map of gross profit, right, with some conditional formatting. So, there’s, you know, several pages in here, different visualization types and whatnot.

27:21
So, if I wanted to move this into something like Power BI or Tableau, for example, I obviously have to understand the Cognos environment and the tools.

27:31
So, what I have to do is go into Reports Studio, which is, you know, this isn’t a tool that you just sort of pick up and use.

27:38
It’s a very robust, fairly complex tool that’s not always, you know, the most intuitive.

27:46
And the first thing I notice is that I have this, this web environment, is that I have just a ton of metadata here, right?

27:54
I have a drill into this HR, and I’ve got nested namespaces, and folders. And, you know, just to get to the data, there’s so much stuff going on here, right? I’ve got an inventory area. I’ve got a retailer’s area, and I have a sales area. Now, fortunately, I understand how to use Report Studio.

28:14
No, I know this report.

28:16
So, I’m able to go into it and say, OK, well, I know that this report has three pages, and I can see here that it has three different queries.

28:26
And then what I have to do is kind of, I kind of have to go into the, the various components here to see where they came from.

28:35
So, I have to drill into each one of these things and see that, oh, yeah, This comes from sales query products, product line.

28:42
Got it.

28:43
So I know that it’s from my Go data warehouse package, and that it comes from sales query, right? Product line.

28:53
So, OK, great. I got that information.

28:56
And now I have to repeat this for everything else. And I see this a sales query time year.

29:01
And the upshot of this is, I know that, OK, you know, maybe I’ve got some calculations in here.

29:06
Here’s a filter that I put in here for just the United States, and I have to do that for each query on each page.

29:14
And then, I transfer all of this information into a large and growing spreadsheet that I’m then going to reference when I go and pull that into the other tool.

29:27
Right, so, now that I’ve sort of done my forensics on the report and I know that OK, all I really need out of this metadata, is this one piece kind of buried down here, This chunk here and I don’t even need all of that for this particular report, then I have to go into the metadata and figure that out first before I can start doing anything.

29:49
And here’s where things get really interesting.

29:52
Now, this, sorry for the eye chart. this is Cognos Framework Manager.

29:57
Yes, it is old. Yes, it is unattractive.

30:01
And, yes, it is very sophisticated, though. So, you can see, I’ve got 2200 objects, just at the sort of top level here.

30:10
And this is their sample model that, that shipped with the product.

30:15
And it, and, it’s actually very robust and complex.

30:18
So, it shows, here’s the package that is used in that report, so, I’d have to drill into that, again, understanding this tool, which is not something, you’re just going to sort of go pick up.

30:29
There’s, you know, in the training for this, is like, a week long, and that just sort of gets you started, And I can see, here’s all the stuff that’s, that’s in that Report Studio report.

30:38
And, I know, I’m just going to want to pull from that, the sales and marketing information that is here, now. I knew where that was, right, I’ve got a million different namespaces and folders in here, but I knew where that was. And, I can go sort of target this stuff.

30:56
Now, it gets more interesting because this is a modeled according to best practices in Cognos where you have multiple layers of abstraction up from the database layer. So, I’m not done by just saying, Oh, yeah, here’s my sales fact. I have to go.

31:16
I have to go find it, Alright? And then here it is in the, in the Business view, which is another layer down.

31:23
And I can see that this fact table is comprised of quantity, unit cost, unit price. And then there’s a couple of calculations in here, right?

31:30
So I have to pull all this information out and make sure I’m capturing that. And that goes into my, again, bourgeoning Spreadsheet.

31:39
And I can see that this, in fact, comes from these all come from, SLOS underscore, sales fact quantity, right.

31:46
They’re all coming from this: SLS Sales Fact, which tells me that I need to go down another layer into the database view into the sales and marketing data. And again, I sort of know where this is, because I’ve been playing with this. And I finally get down to, here’s my database, great Outdoors Warehouse. And here’s my select star statement.

32:09
Now, you know, imagine things like, again, you have calculations here.

32:14
You might have things like parameters and macros, and custom SQL.

32:22
There’s a lot of stuff that goes into this, That, again, you have to sort of know the tool, Probably put this into a large and bourgeoning spreadsheet, and then go in and recreate this in your target environment.

32:37
And now, imagine you have to do that for multiple packages, models, times, and number of reports.

32:46
And you start to, you know, you start to look like that poor woman in the slide where you’re like, Wow, what a, you know, this thing is, big and scary, Self And I’m not even going to show like rebuilding it in the, in the next environment. Right.

32:59
So that’s just a simple example, you know, relatively simple example. And I’m not even showing you all the pain and suffering that goes into that.

33:06
So, this is what inspired us to create the migration assistant utility.

33:12
And, the way this works is, and I’m sort of showing this in desktop, so, it’s a little, cleaner on the web but, I wanted to use desktop here for, for purposes of this webinar.

33:23
Is, again, we’ve, introspective the Cognos environment, type that into a database and then we provide this report that provides an overview of the entire Cognos environment then, gives you different pages that allow you to drill into reports, executions and relationships, depending on your use case and what you’re trying to accomplish.

33:48
Now, you have a drop-down here that shows all of the packages, and, you can further, you know, drill down and select the specific paths you can multi select.

33:58
In this case, I’ve just selected my two, you know, my two main packages, which is my go data warehouse, the one that my reports in, and might go sales query for purposes of showing the overview.

34:09
What I have statistics on those two packages, as it pertains to the entire environment, right? So it shows me I have six packages. 114 namespaces of which I’m using for and I’m not using 110.

34:23
So, 217 query subjects using 12, not using 205 14 data sources, right? You get the idea. I have this overview.

34:31
So I have a whole lot of information here that is useful in terms of me being able to rationalize and there’s probably a lot of stuff I can cut out of here.

34:43
And the way this works is, every time I select something in here, it cross filters everything else. So you’re able to go.

34:52
You’re able to do sort of a top-down approach, where if I pick a report, it cross filters.

34:59
All these other ones where I can see, oh, yeah, this comes from this namespace in the model, comes from these four query subjects and uses these four underlying tables.

35:09
So, you just saved me the trip out to Cognos? So, to look at that to see where does that metadata come from.

35:15
And it comes from, they go data warehouse from, you know, those tables. And it’s the Data Source Code Data Warehouse, it’s a SQL Server database.

35:23
And it’s the Schema Go sales DW, right? So, very easy for me to go sort of top-down, or from a report down.

35:32
Or if I wanted to do that, you know, that data source migration scenario, I can pick go Data Warehouse, and it’s going to filter sort of upwards, right? So, I can see, here’s all the tables in that data source. Here are the upstream model query subjects and namespaces.

35:47
And here are all the reports that are touched by that, along with usage statistics.

35:53
Right now, I don’t have any usage statistics in here for this one, because it’s, it’s sort of demo data, but you would be able to see that.

36:01
Now, going into the Reports tab, again, you have something similar here, where I can go in and pick, I can filter by a particular package. I can search for a particular report, right?

36:19
And if I pick a report, it gives me, this is where I get that recipe book, of what that report was made of. Now, remember, that report had three pages. It had three queries in it.

36:31
And it had the various objects, right?

36:34
my map, my pie chart, the list, the combo, and which queries those belong to, as well as the query items, the filter expressions, and then the underlying tables that are used in that report.

36:46
So this is that recipe book that says, you know what if I want to rebuild Todd report? I don’t have to go into all those tools and figure all this stuff out.

36:54
I can just pull this up here and I get a summary of it.

36:57
And for example, if I pick a query item, I can then go look at the lineage, the lineage.

37:04
And when I go into the lineage Analysis for Product line, I can see I can track this back through to the model and see that this, you know, ultimately goes down, too.

37:15
This sales query products, product line, through the Business View, to the Model Query subjects, Product line, data item.

37:26
Returning back to this, I can go over to the executions page.

37:31
And now, this is where I can define a date range here. Oh, this program not get any usage here because I don’t have my dates, right?

37:40
Sorry, guys, Bear with me here. Update that.

37:44
No, still nothing.

37:46
And what this does is again, allows me to look at all the various packages.

37:51
And I can see that I can sort by the run count, and I’m looking at a report and I can see who used it, right? So I can see what reports re-use, which reports were not used, and who all my users are and when they ran those and was it recent.

38:08
So by doing this, is all this, this audit information. I can see, how many reports I have, who uses them.

38:15
When they use them? Do they use them frequently?

38:17
Have they not use them? Have they not used infrequently? So this really helps with that rationalization exercise.

38:23
And then the last button over here is my relationships tab where this is really all about that model and again, remembering that you have these various levels of abstraction.

38:36
You have the ability to pick, know, your package or packages and sort of focus that information. So, I’m going to go back onto my data Warehouse and focus on that.

38:46
I can see all the important information in here by all objects, or I can focus on the model layer, or the database layer.

38:55
So, if I’m looking at the model layer for example, and I look at products, I can see that products come you know.

39:03
Joins using this cardinality on the left side, on the right side to sales, it’s one to many and what that join criteria is. So, right. You have an equi join, a non equi join. If it’s an inner join, an outer join, whatever that is. and then you can see the other items that it joints too. So, it’s connected to product forecast and return items and I can see that all the way down to the expression in the database.

39:30
So, this is great because it saves you from really having to go through all that complexity in framework manager.

39:37
And, it’s just right here for you in a report that we provide to you as a default output from the system.

39:45
So, again, you can see, I can just go to this report and I have the recipe for all this stuff instead of having to go through the pain and agony of doing all the forensics in Cognos, putting it in a spreadsheet.

39:57
And then, finally, you know, doing that in, then, in the new tool.

40:01
Now, the and we do also have, as I said, before, a set of reports that we provide for Cognos as well, that, Sorry, I lost my place here.

40:16
Now, so, that’s the demonstration.

40:19
Getting back to the presentation here, the requirements for the tool. Pretty straightforward.

40:25
We support cardinal’s talent higher, including OEM versions.

40:30
We knew it requires a SQL Server, and SQL Server Management Studio 2016 or higher.

40:36
And if you want to use the Power BI report, obviously, you need Power BI Desktop, and you can just use the free version for that.

40:43
A little bit more about the Migration Assistant. Todd mentioned this earlier. We have a case study up Senturus.com, under Products’ Cognos Migration Assistant, you can see the link there.

40:54
We have a sample dashboard up there, and there is a demonstration, so it’s actually very It’s similar to this. I don’t think it goes into the stuff I did, and framework manager and report studio.

41:06
We are doing a comparison video that’ll be up there shortly on that, but there’s a lot of information up there, and there are other webinars on migrations that Todd mentioned in the Knowledge Center. So go check that out.

41:18
So, the way we sort of see migrations started going down, our, and then we can help you at Senturus or us at any point along the line.

41:31
Here is, if you want to talk to us about this? We can, we’ll have a conversation with you.

41:36
Talk about the business needs, and the over all migration, for an hour, and then we’ll do another technical discovery with you to kind of understand the versions and, you know, the feeds and speeds, if you will.

41:50
And then that kind of flows down into more of that, you know, Again, That, that assess.

41:54
And, or, Estimate part of The process, where the assessment is a one week engagement, where we run the tool, and we provide you us, that summary view, that first page, sorta, that I showed you, with sort of rough order of magnitude, scope, and costs.

42:16
The thing I didn’t show today, because it’s We’re still working on the visualizations for that a little bit, was we also do the Migration Assistant, also, um, does a report and model complexity scoring, and as an output.

42:34
And that’s something that you can sort of tweak, right? You give weight to complex queries, or, you know, the number of queries get points, and we score it.

42:42
And then you can sort of bucket those.

42:43
And that really helps to say, OK, you know, your environment is 50%, simple reports, 25% medium reports, and 25%, very complex reports, same for your models, And that helps you really kind of get an idea of what that’s going to look like.

42:58
And then, the estimate is a slightly longer engagement at three weeks, where it where it dives deeper and doesn’t just provide that rough order of magnitude and rough cost idea but gets drills down into the specific resource types that might be needed.

43:13
In a rough task outline to give you a much more precise estimate of duration and costs with the idea of you know, then being able to you know, do that go no-go decision and seek budgetary approval for what you ultimately land on, then there’s then you move into the actual execution of that project where you have the design, build, test, and deploy, So you migrate the data layer, then you migrate the semantic layer, and you migrate the report layer.

43:41
Alright, that’s, sort of a high level look of, of what that looks like.

43:45
Now, your mileage may vary, depending on, know, what your use case is and what your environment is, and all that sort of stuff.

43:51
But this is sort of the, the process there.

43:56
Scott, did I miss anything there, anything you want to add, or?

44:00
No, I think you captured it quite well, Mike. I guess, one of the things that I’ll call out, and it’s. No.

44:10
For the of the attendees. I’m one of the people who takes these calls, these, you know, business, need coal and the technical discovery call with folks like Todd and Mike.

44:22
one of the big things that comes up, a big question that comes up is how long is this going to take us? And it’s, a really, it’s a difficult question to ask, and I think it’s one.

44:32
It’s one of the questions that requires a thoughtful approach.

44:39
If I think Capac, back to the last four, maybe five calls I’ve had when we talk to clients looking at, you know, trying to unwind their own report inventories, and they usually come to us after they’ve taken a look at a few to try to see how long would it take for them to rebuild them in another tool? or to upgrade them to a new version of a report.

45:03
Usually, that number is somewhere in the range of 2 to five days for kind of a medium complexity report up to 30 days.

45:13
What we’ve been able to kind of accomplish with the migration assistant is to just bring that number down per report, kind of in the 2 to 4 hour range.

45:24
And the profiling that Mike was talking about is really key there.

45:28
So, as you, everyone on this call can imagine, you know, the inputs into that kind of algorithm really make a big difference.

45:37
So imagine you have one thousand reports, and it takes you four hours to migrate.

45:42
Every single one of those thousand reports, You’re looking at two man years there, or two person years.

45:48
So, it’s that it’s an overwhelming amount of work.

45:52
So it’s really key that you have the discussion about profiling those reports.

45:57
So you can understand which ones need to be archived, Which ones can be rebuilt by end users, which ones have, you know, similar kind of structure or similar query patterns? And all of this is really kind of an engineering exercise in and of itself.

46:15
So, because there is no button that is available for you to just push a button and move a report from one place to the next or from one version up to the next version of Cognos, you really want to spend a little bit of effort in the upfront engineering of the migration.

46:33
Because your benefit, how you get paid back is usually in the tune of thousands of hours, you know, spread across your organization, or other organizations that help you help you complete those migrations.

47:04
Yeah, I mean, again, that Frank Lloyd Wright quote is very apt, where you can change the pencil, a fire, or change it with a wrecking bar at the job site.

47:19
I’ll say, you know, we’ve been through this so many times. We know our way through the forest.

47:26
We can certainly point out things to look for.

47:29
Those 1 and 2 meetings upfront are a great way to share a little bit of that information and find out if you’re planning a migration or planning an upgrade it’s a great way to get started and to see if it makes sense for you to engage in.

47:48
So great, Thanks a lot, Scott, appreciate it.

47:51
We’ve got a lot of questions in the question pane and we’ve got to close in about 10 minutes. If you have questions, go ahead and put them in the question pane there and we’ll get to those in just a few minutes but stick around here for through a couple of slides about sent tourists and great additional resources.

48:11
We do provide hundreds of free resources, just like this webinar on our website, and we’ve been committed to sharing our BI expertise for well over a decade now, So again, Senturus.com/resources, where you can also see recordings of other webinars.

48:26
As I mentioned, the aforementioned webinars on migrations, migration.

48:35
Other great blogs on that site, you can see all of our upcoming events and various demos of our products, including the Migration Assistant. So head over there and bookmark that.
48:45
Speaking of upcoming events, we have a webinar on Friday, February 4th on 4 simple mistakes that equate to big reporting errors.

48:56
So, these are common data modelling and It things that that it’s across BI tools where you can introduce mistakes and how to avoid those things.

49:10
And that’ll be by our trainer Patrick Powers.

49:14
So, go and register for that, and please join us.

49:17
Quick note about training. We do offer training.

49:20
Our teachers are, again, those unicorns that are fluent in multiple platforms.

49:24
We offer training in Power BI Tableau and IBM Cognos.

49:28
We offer tailor group sessions, small group mentoring, instructor led online courses, and self paced e-learning and we can tailor that to meet your organization’s needs and sort of mix and match those different types of training.

49:41
At Senturus, we concentrate our expertise on business intelligence with a depth of knowledge across the entire BI stack.

49:50
And our clients know us for providing clarity from the chaos of complex business requirements, disparate data sources, and constantly moving targets, made a name for ourselves because of our strength at Bridging the gap between IT and business users.

50:02
We deliver solutions that give you access to reliable analysis ready data across the organization. So you can quickly and easily get answers at the point of impact in the form of the decisions you make and the actions you take.

50:12
And we also help people with the aforementioned migrations in all of their various flavors.

50:19
We do offer a full spectrum of services, as you can see here. I’ll let you read the slide.

50:24
Our consultants are leading experts in the field of analytics with years of pragmatic, real-world expertise, and experience advancing the state-of-the-art.

50:32
And we’re so confident in both our team and our methodology that we back our projects with a 100% money back guarantee that is unique in the industry.

50:41
And, as I think I mentioned before, we’ve been doing this for a long time, 21 plus years, With successful projects. Over 3000 of those, across 1550 plus clients. No doubt, you recognize many of those logos here. We’ve worked across just about every industry, and several functional areas, most functional areas, including office of finance, sales and marketing, manufacturing, operations, HR, and IT.

51:04
We pride ourselves in that, in being large enough to meet all of your business analytics needs.

51:08
That’s small enough to provide personalized attention.

51:12
If you’re interested in joining us, we are looking for following positions there.

51:17
Look at the job descriptions on the Senturus site, and you can e-mail us your resume at [email protected].

51:28
And with that, we come to the Q & A.

51:40
Anyway, I just wanted to speak to a couple of questions about the mighty folders my content, and that does get captured. It does a full scan out. The content starts. So, you do get 100% visibility into all that. My user folder content that’s difficult sometimes to get a grasp on as to what’s out there and what people are doing, so that’s fully included in the inventory.

52:04
Are you able to pull up the Power BI workbook?

52:10
There’s some questions about looking at some of the calculated expressions, so like if you were to look at one of the audit reports, any of those have a couple pretty good ones.

52:25
You only look under the audit package on package.

52:30
OK, and let’s do one of these, I think you can see it right in the middle, there, So you got, complex expressions, if, then, functions, case functions, you know, a thing you could throw at it.

52:42
Again, you’d have to go into the query, and double click, and copy and paste this. You can get all that. All that good stuff, you know, all in one screen, just clicking reports.

52:51
They’re easy to find.

52:54
So that’s another question someone had. Yeah, that was a good one. Kind of ugly.

53:00
You wouldn’t want to re type that, if you don’t have to.

53:05
A lot of people in this webinar, based on the poll we’re looking at more of just cleaning up their Cognos environment, and I know we had this originally, you know, focused on migrations.

53:17
Just to speak to that a bit more that audit information is going to be extremely valuable. I know I’ve had lots of customers over the years who have asked, You know what? You want to clean up a lot of these old reports. what’s being is what isn’t being used in the audit data is a good start because you mentioned earlier, you know? It’s on demand, so if I run a report, it’s in there. It tells me when I ran it, so if I read it five years ago and I still have that data, I can see, it hasn’t been run in five years and I ran it and that’s it.

53:47
For all those reports that haven’t been run, you’re limited as to what you can see as far as that. So, because we do that full inventory, we capture every single report. We then sync it up with that audit data.

53:58
We can see all the reports that haven’t been run that are just sitting out there not being used and those are easy candidates to just archives. We can grab all those either delete them, put them in a folder export it in archive, and get it out of the system. So, that’s a very quick and easy way to, to clean up your Cognos environments. talking about, thousands tens of thousands of reports. Just get all that junk out there.

54:19
Try the other use cases that we’ve had for people staying on Cognos and cleaning it up.

54:27
Essentially, because in my mind, I think of, how I would use a tool? Or then we get involved with the customer, and they’ve got just different ways of thinking of going about things.

54:35
One of the interesting ones we did was a reverse lineage. When I think of calculus, I look at the report and I want to get down to the database.

54:43
We just finished a project with a client that wanted to do the reverse, They wanted to say, show me all the reports that are hitting this table in this field. And because we have that full path of no report to the database, we could actually go the reverse method.

54:58
And they say, we found 25 reports that are all referencing this field. And it gives you an impact analysis as if I was delete this table, or make a change to this field.

55:08
What would that do to my Cognos environment? What reports would be affected by it? Maybe it’s a major impact that tens or hundreds, thousands of reports are going to be, are massively affected by removing or changing something in a table.

55:20
So, interesting, as its product matures, to see the different ways we can slice and dice this information and look at your Cognos environment in different ways.

55:37
Somebody had asked about that while we use the Power BI dashboard, we do have, as Mike mentioned earlier, too, corresponding Cognos reports that look similar to the Power BI workbook that Mike was showing. It’s a matter of preference.

55:54
We can work with either one, the Power BI, just a little easier because it’s portable. We can take a file and load it right into Power BI and pointed out your database and that’s it. The Cognos obviously to get stuff into Cognos It’s a bit more work. You have to create those deployment packages in an FM model or data module and the reports that go with it this a little bit more administration in Cognos involved, but we definitely have a bunch of reports that we can show you as well if you’re interested. And you want to use Cognos instead of Power BI.

56:47
That’s something that we generate as part of an engagement and assessment.

57:05
If you want to play around with it and explore, it is on the Senturus.com website under Products, and then click the Migration Assistant.

57:29
So, I’ll drop this link Into the chat.

57:42
We’re happy to discuss that sort of stuff with you.

57:48
Many of you have reached out to us in the chat there, but we want to make sure you go ahead and reach out to us. You can reach us at [email protected]. You can call us if anyone still picks up the phone anymore.

58:00
And we’d like to, first of all, thank Todd and Song for helping out and providing great question answering an commentary, and thank all of you for taking time out of your undoubtedly busy schedules to join us on this Knowledge Series event.

58:16
So, we hope to see you on an upcoming event, or to hear from you about your migration or business analytics needs.

Read more