TECHNOLOGIES COVERED

Cognos, Microsoft Power BI

PRESENTER

Michael Weinhauer
Practice Area Director and Solutions Architect
Senturus, Inc.

Michael’s team is responsible for the development of the Senturus Analytics Connector. He also heads up our Tableau Practice. Before Senturus, Michael spent over 20 years in different roles at Oracle, IBM and SAP acquiring a wealth of hands-on, practical BI and big data experience.

PRESENTATION TRANSCRIPT

[drawer title='' instructions='▼  Introduction']

Greetings everyone, and welcome to the latest installment of the Senturus Knowledge Series. Today we're going discuss using Power BI with your trusted Cognos data leveraging our solution, the Senturus Analytics Connector for Power BI.

For our agenda today, I'll give you an overview of the Senturus Analytics Connector, followed by a demo of the product and some frequently asked questions. I'll wrap up with an overview of Senturus, give you some additional great resources and answer your questions.

I am Mike Weinhauer, the practice area director for our Tableau practice and solution architect. I've been delivering, selling and implementing analytics solutions for the 20-plus years of my career. I'm the product manager as well for the Analytics Connector. So this is my baby.

I would like to take a poll to get to know the audience. What capabilities of the Senturus Analytics Connector interest you. Do you want to leverage your existing Cognos metadata and the business rules that have been carefully curated and modeled in your Cognos Framework Manager models and reports? Are you interested in leveraging the security that you've carefully baked into those models/ reports? Are you interested in avoiding performance problems from poorly modeled data in Power BI? Or is it really a question of access? In a lot of cases organizations won't give individual users real access to data assets like the enterprise data warehouse, much less transactional operational systems, and you want to give them the ability to access that corporate data that's accessible only via something like Cognos. Or is there another reason?

So more than half of you want to leverage the existing metadata, only 40 percent around the security. Performance issues at 34%. And then really accessing that corporate data tops it out at 60 percent. But you can see a pretty good spread across all those different options. Not super surprising results, but always interesting to see what people are thinking. And then 25 percent other. Thank you for sharing your insights and participating in the poll.[/drawer][drawer title='' instructions='▼  Overview of the Senturus Analytics Connector']

So, let's get into an overview of the Analytics Connector.

The business problem we saw was that organizations have invested heavily in existing Framework Manager models and/or reports that provide a curated set of data. It's governed, it's secure, it's business friendly, and it's highly performant. The problem is because Power BI does not have access to that modeling layer and those reports, users end up either using the Cognos enterprise BI for ETL and dumping out flat files and spreadsheets or they attempt to recreate those models in Power BI's data pane, which at a minimum wastes time and at worst can compromise the integrity of the data and security. This same challenge is also true with Tableau and we have a Connector solution for that as well.

The crux of the issue is that these modern visualization and data discovery tools have their own metadata layer built into the files. And unless you carefully manage that, you can end up with dozens, hundreds or even thousands of these things. There's no guarantee that people are going to get these things right.

The short list of reasons why it’s a bad idea to recreate metadata that's already been curated in your Cognos environment is that it's time consuming. If your users even have access to it. Typically it's gated by IT or those business owners or those asset owners for various reasons, usually for security and oftentimes for performance. Those systems are very complex. They have hundreds or thousands or tens of thousands of tables (if you're talking about an ERP system.) Business users and analysts have little or no knowledge of the tables, how those tables relate and/ or the join types. All this leads to the problem of potentially compromising data integrity.

Additionally, you can have real change management nightmares if these things aren't carefully managed at the outset. When you have metadata at the file layer and if you have Power BI files and Power BI workbooks, Tableau workbooks, etc, you have to chase and manage changes through those workbooks. Doing this can become a real nightmare for the organization.

Also, performance can really suffer as people start to add joins and data sources if they're not getting index columns, or partition elimination, etc., depending on that data source. There's a host of other challenges around the the amount of audit trail that you have, such as data quality, data typing.

Security becomes a really huge issue as well. In this era of GDPR and PII and HIPA and FERPA (and you name it, depending on your vertical industry) and the level of government regulation, you're under, data security is a big deal. At a minimum what ends up happening is people have to remodel that data security in Power BI, which is again a waste of time if you've already done it in Cognos. Or, they end up doing an end run around it by getting an export of a flat file or an Excel spreadsheet and calling it a day. There's a lot of risks around these type of issues that our Connector helps to address.

And finally there is the age old problem of data silos. People coming to meetings and arguing over whose numbers are right. You're simply trading up an Excel spreadsheet for a prettier version in the form of Power BI and arguing over which pretty graph or chart got their metadata layer behind that correct.

We saw that void and stepped into it with the Analytics Connector. It gives Power BI users the ability to connect directly to the Cognos application layer and get instant access to high quality data sets.

How do we do that? You'll see on this diagram (you Cognos users will recognize this immediately as the Great Outdoors sales model) that it has many namespaces all the way from the basic database view to up through the abstraction layers into sort of subject areas around sales and inventory and forecasting and whatnot. Those are exposed via packages that Cognos publishes out that the end users then create content from in Cognos.

Here's what we did with our Connector. Now, looking at the Power BI side, we log into Power BI and those namespaces and core subjects are exposed in the navigator pane. You simply then choose your objects and those become available for you.

So, you don't have to sacrifice the integrity and security of your data for the agility and the beauty that is the reason that your users are adopting things like Power BI and or Tableau.

Finally, you can publish visualizations and data sets to Power BI online thereby enabling the rest of your organization to either consume existing visualizations or to empower end user and analysts to construct their own visualizations from existing visualizations or from the data set they you have published from desktop.

Currently, on-premise Power BI server is not supported owing to the fact the Microsoft does not support custom connectors, which is what we use, for the on-premise version of Power BI server. It is not on their roadmap. We are pushing for them to support it, but it is not currently.

The Connector becomes a very powerful tool for managing and maintaining governance and security while giving people flexibility in these bi-modal or multimodal BI environments. In other words, it lets you support Cognos and/or other tools.

In summary, the Senturus Analytics Connector gives you faster, more accurate reporting. Why? Because Power BI analysts spend their time doing actual analysis instead of manipulating and curating data sets or remodeling data that you've already done in Cognos.

You reduce the costs and risk around change management because the changes in Framework Manager models are managed and they're picked up by the Connector, which eliminates chasing those through all those different files.

You get better trust in the data which leads to increase in adoption. The two biggest factors driving adoption in organizations are trust in the data -- do they believe the data that they see in your Power BI dashboards -- and the performance.

Security is obviously another big deal. You eliminate redundant application of security and/or the risks associated the use of flat files to do security end runs around.

And, you get better Power BI performance owing to avoiding the pitfalls of poorly modeled data in Power BI.[/drawer][drawer title='' instructions='▼  Demo']

Let's look at what this looks like in real time. I'm going over here to my server. From my Power BI desktop, I'm going to click on Get Data. I've got various options here, one of them being the Senturus Analytics Connector. I'm going to log into my server. Here's the server name, and this is the name of the package. We've used this custom query because it allows us to use what's called direct query mode. So this will allow for direct queries. I'm going to log in and this is a kind of first level of authentication where it's going to say, "do I even have access to that Cognos environment?" And if I do, do I have access to the package? Which in this case I do, I have access to the go sales query. It's going to expose all those namespaces as I showed you in the slides.

For those of you who are maybe not familiar with Cognos, these are what we call presentation layer query subjects. The complexity of the underlying Go Sales database, which is an OLTP database, is again masked back through a couple of layers of the Cognos application. And then we only expose the presentation layer stuff, which has been grouped into business friendly buckets. So I have dimensionality and measures and whatnot in the form of my branches, my order methods, products, sales and time.

And so what this is doing now is it's actually parsing through that metadata on the Cognos side and evaluating those query subjects. This takes just a second here while it's going through its machinations. It's pulling those into the model, at which point I then go over to the model and you can see that I have my measures and here's all my different dimensions. Now the challenge that we have here is because these have been carefully modeled, the join criteria that's buried three layers deep in my model is not exposed in these core subjects, is it?

What we've done to get around that-- because Power BI and Tableau and all these other tools require that when you create this data model that you have to have a relationship between your tables, right? Otherwise you'd get all Cartesian products. So, we sort of created dummy links, a Triple A underscore link, that we use as the relationship and then we ignore those when we pass the queries back to Cognos from Power BI. This is something you would do as a one-time exercise. Or you'd minimize this because you save this and then it's done and/or you publish this up to the Power BI server or Power BI online. So this isn't something you do every time.

So once that's done, now I've got this nice polished data set and the rest of this becomes a Power BI exercise. So now I'm going over here and I'm clicking on my revenue and my country. And this automatically creates a nice little map visualization for me. I'll sort of drag this out here and you can see the little spinning icon here. So it's executing that query against Cognos.

I'm going to add in here a little grid and I'm going to shrink that thing down a little bit. I'm going to go into Products just to show you that you can do things like build a quick hierarchy here with Product Type and Product Line and then I'm going to drop that hierarchy in and along with my Revenue and my Plan Revenue. And again, you can see that thing spinning there, that's going to go execute that query.

I'll show you that you can do at a time series analysis here. So I've got my time dimension. I'm going to grab my Month and my Year and this time I'll throw in my quantity for example. And so you can see that this query is happening in real time. When I click on Mountaineering Equipment, it's executing the cross filters across the different visualizations and I can do things like expand this down to the next level and do all the data exploration that you'd expect including hierarchies and whatnot in Power BI.

I can then publish this report up to Power BI on the web using the enterprise gateway. And when I publish that -- I'm going to just toggle over here and sort of Martha Stewart this and show you the fully baked turkey -- now this is over here in my workspace. So I have workbooks and data sets that have all been pushed up to Power BI.

In summary, the recreation of metadata that you've already curated in Cognos is, at a minimum, slow. At worst it's challenging and very risky. The Analytics Connector provides agility while maintaining the integrity and security of your data. So, your analysts can spend time doing actual analysis instead of prepping data that's already been curated somewhere else.[/drawer][drawer title='' instructions='▼  FAQs']

Now to address questions we frequently get asked:

What is the connectivity method that we use? We use a custom Connector as you may have surmised from the demo. The reason we do that is to support that direct query mode. That means the queries are being issued impersonating a user in Cognos and you're issuing queries against the Cognos system. And really the power of that is to push off the query processing to Cognos and to leverage the security that's already built in, automatically apply that.

We support IBM Cognos in 10.1 or higher and Cognos Analytics 11, all versions. We do support cloud installations of Cognos as long as you can reach the dispatcher, which is generally not a problem. That's how we communicate to Cognos using our Connector.

In terms of the data sources behind Cognos, we support relational data sources, PowerCubes, DMR, dynamic cubes, and TM1. Those are very proprietary data sources, and there's really no way to get at PowerCubes or dynamic cubes. And it's tricky to get at the other ones without using something like our Analytics Connector. So it has a unique position in the market in that it enables you to do that and we do support reports as well. So you can access Congos reports , if you have business logic that is resolved in those reports, or hasn't been pushed into the model where it's just a simpler data set -- for whatever reason -- you can configure the Connector to use reports.

The product's been in development for over three years. We're currently on version 3.0. We're about to issue a new release, but the product's been in development for over three years. We released it GA in November of 2016. The Power BI functionality is newer owing mostly to Power BI supporting these custom connectors. That's relatively new functionality for them. And then the enterprise gateway is literally something that came out this month.

We support Power BI desktop versions 2.5x and higher. In order to use the enterprise gateway, you want to be on the most current release, which is that September release. We support all the different flavors of Microsoft that are out there.

Basically, if you can run Power BI, you can run our Connector on it. Our Connector lives wherever connectivity to Cognos is required. In other words, it sits on that Power BI environment. There's nothing that gets installed on the Cognos servers. It is also available as a client server installation for those environments where you want to centralize that or offload that processing or you have a really tightly locked down client environment. Ask us about that if you're interested in it.

In terms of the software requirements ,it has about a 200 megabyte footprint. It has some nominal processing overhead, because it's really a sophisticated kind of ODBC JDBC bridge with a lightweight in-memory database that allows us to support the full array of Power BI capabilities. Any environment that can run Power BI can definitely support the processing requirements of our Connector.

It's important to understand from a Cognos environment impact perspective that Power BI users using our Connector will generally interact with the data kind of like I did: They might be creating content, drilling up, drilling down, there'll be putting more load on that Cognos server. In Cognos speak, it's kind of like an interactive report user and you definitely want to assess your use cases and look at what kind of burden those folks are putting on that system to make sure you size the Cognos environment appropriately.

Security. We do enforce any of the security applied within the Cognos environment. I tried to highlight those as I was going to the demonstration. There's multiple checkpoints along the way.

The login says, do I even have access to Cognos, do I have access to that package or that report? And then within that any data security that you've already modeled would be applied because, again, you were impersonating that Cognos user based upon the login that is provided.

In terms of Framework Manager modeling requirements. We don't require any special modeling requirements for the Connector. You can take your model as is. Joins get created between query subjects that you pull in. And we use those dummy links. You can override those if you have a relationship that you want to set up, if there's some business need for that, as long as those make sense then those queries will execute in Cognos. Then, you can certainly do that and we will respect those.

We do support both Cognos classic query mode and dynamic query mode. That's really kind of a transparent to us behind the scenes. Again, we're impersonating a user. We create a report specification based upon the query that needs to be issued, execute that against Cognos and pass that back to Power BI for rendering. We use the method for accessing Cognos, an ODBC JDBC connection. So we take an ODBC connection via that custom Connector, convert that to a JDBC call to Cognos and then use that custom Connector functionality to facilitate that along with our proprietary code. The support for the product is provided by Senturus and is included in the price of the product.

So you're probably nodding your head thinking yeah, this makes a lot of sense, right? Thinking, we have this bi-modal tri-modal, multi-modal BI environment, and we're trying to figure out a way to access this data and stop people from dumping out big flat files and wasting time. And you've got to go back to your boss or your boss's boss or a committee or whatever and figure out how to pay for this thing.

Well, there's some handy ROI knobs and levers that we give you. One of the biggest ones are the time and money that's required to create metadata from scratch in Power BI. And if you really think about that and you just pencil it out on a napkin or an Excel spreadsheet that really ranges from hours to weeks or more, depending on the number and complexity of those packages and reports. You think about how much time it took to build those Framework Manager models and then a report on top of that, if you're going to use one of those, and it could be to all the way up to maybe impossible. If you're trying to get up a PowerCube or a dynamic cube, there's only one way to do that. You're not going to get to that data.

Then when you add to that the risk of not having proper data security applied, then that's a harder one to pencil out because those dollars get pretty high pretty quickly. If people are dumping out flat files and spreadsheets, there's all kinds of GDPR, PII, etc, risks around that. Then add to that the time saved by converting reports to Power BI, making them faster and easier. Add to that the time potentially wasted and risk of having wrong business metrics as a result of people not getting the metadata layers right and/or missing changes and things like that. It pencils out really quickly when you look at the absolute dollars required to leverage our Connector.[/drawer][drawer title='' instructions='▼  Pricing']

In terms of pricing, there is perpetual licensing and subscription licensing available. From a perpetual standpoint, Desktop pricing is $200 per user. Server is half of that at $100 per user. And then maintenance is 25 percent of that beyond year one cost. If you're interested in a subscription model, then it's $85 per user per year for Desktop, $45 per Server user per year. If you're a Desktop user, you don't have to pay again for a Server user. Talk to us about the pricing stuff.

We do offer volume discounts and there's a $5,000 minimum purchase required for the Connector. From a licensing requirements perspective on the Cognos side, if you're doing those direct queries, you need to be a Cognos Analytics user or equivalent. If you're doing an import into the Power BI proprietary data file, then you need to be a Cognos Analytics user for the purposes of importing. But then anyone accessing it subsequently obviously doesn't require a Cognos license. It's a proprietary data source. They would just need our Analytics Connector license for that security.[/drawer][drawer title='' instructions='▼  Free Test Drive']

We do offer a Connector test drive, which is a complimentary. It's a two week proof of concept where we'll come into your environment, get on a web meeting, configure and make sure data is flowing through the Connector for you. And if you're in the IT group, we highly recommend you do that in tandem with a business user, replicating a business use case so that you really get to see the value in it. We'll do this for you free of charge. It's great. It takes half an hour to 60 minutes to get the Connector configured up and running in your environment. We set up a few checkpoints and you run it through its paces against your evaluation criteria.

It's pretty simple process to really feel comfortable with it. The nice part of that is that you can flip the switch and put that into production pretty quickly and start getting value out of it very rapidly.[/drawer][drawer title='' instructions='▼  Q&A Part1']

So now I'm going to jump over here to the question log here.

First is "so what's the difference between this and TM1 Connect?" TM1 Connect is a product, a competitive offering, that allows you to connect directly to TM1 cubes. That's focused on connecting directly to TM1; our Connector connects to TM1 through Cognos. That's an important difference. If you just have TM1 data assets, maybe that's your answer. If you have TM1 and other data assets through Cognos, which is more likely the case, then you should be looking at us. So we said what data sources are allowed, again, as you look behind Cognos, it's totally transparent; but we do support relational databases as well as dimensionally modeled relational PowerCubes, dynamic cubes, TM1 and reports. We do support Powerplay cubes.

"How can we hide the source so users cannot do changes to the report created in Power BI, like in Cognos for the users with no edit access?" If you secure that, publish that up like I did to the Power BI server, or to Power BI online, then you can have consumer level access to that where people can just access that report and they're not able to modify the metadata.

"Can you use Power BI instead of Cognos Analysis Studio?" That's a great example of precisely why this product exists. So in this case where you're thinking that Analysis Studio is a little long in the tooth and it doesn't of do all the things I want it to do. I want to have a map visualization or X, Y, and Z. It's a great example of where I want to use a multidimensional source or a Cognos source, but I want to use Power BI or Tableau against it. (Our Connector does work for Tableau as well.)

"Will it use the Cognos SQL query engine and some Power BI visualization rendering." That's exactly what it does. The mechanism is is that you are impersonating a Cognos user in Power BI. When you log in and I create a Power BI visualization and say I want revenue over time -- it is literally going back to Cognos and creating a report specification based upon the query we submit to Cognos saying select revenue and sales. And then that gets rationalized through the Cognos package, through the metadata layer. All that query processing underneath happens. The results gets passed up, handed back to Power BI and then rendered. Now of course, Power BI has caches and has calculation capabilities and whatnot that it uses, just like Tableau does, but the initial queries and then to the extent needed, it's going to go back to Cognos and use Cognos's SQL engine and/or or the database engine.

"How does the licensing work with named users?" If you are doing live connections then you have to have a Cognos license and a Connector license because you are creating live queries. It is as if you are a Report Studio or Workspace Advanced user where you have the permission to get the Cognos metadata and then create a report specification behind the scenes, execute that and do that all in Power BI. So that's how it works in terms of Cognos licensing. If you're using that import functionality in Power BI, then you just have to be that BI analysis user to create the import and then you need a Connector license because that's a proprietary data source.

"Does the Connector work with Tableau?" Yes. So that it works the same way. We have webinars on that as well as three different webinars with customer case studies on using our Connector. I encourage you to go to the Resources section of our website and check those out.

"Do you support a shared environment for Power BI professional versions? One person can connect to a Cognos package. The rest can develop a different dashboard." Yes. You can take that and publish that up to the Power BI online or Power BI server using that enterprise gateway. Now that is brand new functionality, that Microsoft just introduced. But we have it up and running in our environment, it does work. Then they can use that data source to create content if you let them or they can simply be set up to be consumers so they can use content that you've already created.

"When publishing a Power BI report, is the data published with the report?" Yes. The data or the data source gets published up with the report. And again, it depends if you are using import or the direct query mode, then it's going to publish up that data source and you have control over how that works.

"If it can use query subjects built in Framework Manager can it use the premade filters built in Framework Manager as well?" I am not 100 percent sure. I believe you can, I need to validate that though. So we'll research that and get back to you.

If you have Power BI and Tableau, do you need two licenses, two Connector?" Generally speaking, yes. But we'll certainly work with you and your environment to make sure that we map the cost of the value that you're getting out of it. So we can have a conversation around this.[/drawer][drawer title='' instructions='▼  Q&A Part2']

"Can I use a Cognos cube and mash it up to other Power BI data sources. How would I create a relationship?" Well, the relationship has to exist. A lot of times the joins and whatnot are our masked in something like a PowerCube. But yeah, you can take one data asset that you can combine with other data sources. But those relationships do have to exist. So something like an order ID or a date or something like that, right? You also ask about connecting to FM packages. That's exactly what you're doing. So what I did in the demonstrations, you're connecting to the Go Sales query package in this case, and then anything that's in that package you can leverage in your Power BI or Tableau environment.

"It works with Power BI server on premise, doesn't it?" Yes, it does.

The $5,000 is a requirement for a subscription. You'd have at least 25 users, 200 purchases." Yeah. So the $5,000 minimum still does apply regardless of whether your perpetual or subscription. And the reason is that we leverage a third party software component under the product that we have to pay them for, so that's, that's the reason why we have to have that minimum. But again, if you look at the $5,000 in the scope of your Power BI or Tableau and Cognos spend, and you look at the value of not of keeping those analysts analyzing instead of recreating data and then eliminating the risk of spreadsheets and flat files flying around, that becomes, you know, the change you find in your sofa relatively speaking.

"What about the impact when Cognos is upgraded." So we were on the Beta programs for Tableau and Power BI and Cognos and we stay on top of that stuff. The impact is pretty negligible. ODBC and the Cognos SDK calls that we make are very stable on both sides. It's pretty streamlined when you upgrade Cognos. We test that out and certify that within a fairly short period of time. Usually we're ahead of it before the customers are and it's really a matter of replacing a couple of files and just running an upgrade process. So it's pretty straightforward.

"Any plans for the Connector to work with Power BI server on prem?" Currently, on-premise Power BI server is not supported owing to the fact the Microsoft does not support custom connectors, which is what we use, for the on-premise version of Power BI server. It is not on their roadmap. We are pushing for them to support it, but it is not currently.

Great. Well it looks like that was a ton of questions. That's great stuff. Thank you for joining us today. And you can always reach us over at our website email at info@senturus.com or call us at 888-601-6010. We have a great content on LinkedIn and Slideshare. Follow our YouTube channel: we have demos of the Connector up there, both for Power BI and Tableau. Follow us on Twitter and Facebook. We hope to talk to you very soon and help you with some of your analytics needs. So thanks and have a great rest of your day.

[/drawer]