Author Topic: Connect Cognos to SSAS Cube  (Read 479 times)

Offline cognostechie

  • Statesman
  • ******
  • Join Date: Jul 2005
  • Posts: 1,672
  • Forum Citizenship: +133/-7
    • Pervisol
Connect Cognos to SSAS Cube
« on: 16 Nov 2021 03:20:01 pm »
I have Cognos 11.1.7 installed and I did install OLEDB Client from this link:

https://docs.microsoft.com/en-us/analysis-services/client-libraries?view=azure-analysis-services-current

I downloaded the 1st one.. MSOLAP (amd64) and installed it. I copied msolap.dll  from C:\Program Files\Microsoft Analysis Services\AS OLEDB\140  to  C:\Program Files (x86)\ibm\cognos\c10\bin64

When I open Framework Manager and run the metadata wizard and select Third Party Data Sources, I don't see any option for SSAS though I do see options for SAP BW, Oracle etc.

Is anything else needed?

Thanks !

Offline MFGF

  • Never knowingly correct
  • Super Moderator
  • Statesman
  • ******
  • Join Date: Jul 2005
  • Posts: 11,376
  • Forum Citizenship: +665/-10
  • Cognos Software Muppet
Re: Connect Cognos to SSAS Cube
« Reply #1 on: 17 Nov 2021 07:12:14 am »
I have Cognos 11.1.7 installed and I did install OLEDB Client from this link:

https://docs.microsoft.com/en-us/analysis-services/client-libraries?view=azure-analysis-services-current

I downloaded the 1st one.. MSOLAP (amd64) and installed it. I copied msolap.dll  from C:\Program Files\Microsoft Analysis Services\AS OLEDB\140  to  C:\Program Files (x86)\ibm\cognos\c10\bin64

When I open Framework Manager and run the metadata wizard and select Third Party Data Sources, I don't see any option for SSAS though I do see options for SAP BW, Oracle etc.

Is anything else needed?

Thanks !

Hi,

I'm assuming you'll be using Compatible Query Mode if you're connecting via a dll? One thing to know is that CQ is 32-bit only, so you'll need the 32-bit client libraries installed, not the 64-bit.

I haven't got a copy of FM to hand currently, but from memory there are specific source types in the list for MS Olap cubes - more than one, if I'm remembering. Have a scroll through the list and see what's available in your case.

Cheers!

MF.
Meep!

Offline cognostechie

  • Statesman
  • ******
  • Join Date: Jul 2005
  • Posts: 1,672
  • Forum Citizenship: +133/-7
    • Pervisol
Re: Connect Cognos to SSAS Cube
« Reply #2 on: 17 Nov 2021 02:40:40 pm »
Hi MF

Thanks for the reply.

I am using Dynamic Query Mode and all existing FM models using DQM are working.

We do have a Data Source made now (made using Data Server connections menu) which is connecting to SSAS 2019 and I can see all SSAS cubes in there. Upon testing, the Data Source fails in the Compatible made but succeeds in the Dynamic mode. When I create a new Framework model and select the Data Source, it give me an error that 'M19 is not supported' which is the same error I get for Compatible mode when testing the Data Source. It seems to be choosing CQM but why? I had selected the DQL option in the very first screen when making the FM model !!

When I choose the option of Third Party Datasources then I don't see anything for SSAS cubes.

Offline MFGF

  • Never knowingly correct
  • Super Moderator
  • Statesman
  • ******
  • Join Date: Jul 2005
  • Posts: 11,376
  • Forum Citizenship: +665/-10
  • Cognos Software Muppet
Re: Connect Cognos to SSAS Cube
« Reply #3 on: 17 Nov 2021 02:48:55 pm »
Hi MF

Thanks for the reply.

I am using Dynamic Query Mode and all existing FM models using DQM are working.

We do have a Data Source made now (made using Data Server connections menu) which is connecting to SSAS 2019 and I can see all SSAS cubes in there. Upon testing, the Data Source fails in the Compatible made but succeeds in the Dynamic mode. When I create a new Framework model and select the Data Source, it give me an error that 'M19 is not supported' which is the same error I get for Compatible mode when testing the Data Source. It seems to be choosing CQM but why? I had selected the DQL option in the very first screen when making the FM model !!

When I choose the option of Third Party Datasources then I don't see anything for SSAS cubes.

Hi,

Can you be specific about exactly how the data server connection is configured? I'd expect the compatible test to fail - Data Server connections are DQM only.

What exact error do you see when importing metadata in FM? Can you tell us exactly what the error message and codes say?

What results do you see if you try to create a data module using the connection? The same errors? Different errors? Does it work?

Cheers!

MF.
Meep!

Offline cognostechie

  • Statesman
  • ******
  • Join Date: Jul 2005
  • Posts: 1,672
  • Forum Citizenship: +133/-7
    • Pervisol
Re: Connect Cognos to SSAS Cube
« Reply #4 on: 17 Nov 2021 03:18:09 pm »
That's exactly I am saying MF. The Data Source connection fails in Compatible mode but succeeds in Dynamic mode. This is the error I get when testing the Data Source from Cognos admin / Configuration page:

http://<server name>:9300/p2pd  / Compatible   Failed QFS-ERR-0138 The data source 'M19' is not supported by any provider.
http://<server name>:9300/p2pd  / Dynamic   Succeeded      XQE-DS-0015 Client property: "MSOLAP.DLL 15.1.48.22".

When I create a FM Model, I get the same error as above.

Creating a Data Module works when I right click on the cubes (seen in the connections window of Data Server).

One thing to note here is that only FM is installed on the server and Content Store and Dispatchers are installed on separate servers. I am wondering if the OLEDB driver needs to be installed on those servers and not FM server? For other FM models the DQM works and that's because it is using the JDBC driver on those servers so maybe it is trying to find the OLEDB for SSAS cube on those servers?
 

Offline cognostechie

  • Statesman
  • ******
  • Join Date: Jul 2005
  • Posts: 1,672
  • Forum Citizenship: +133/-7
    • Pervisol
Re: Connect Cognos to SSAS Cube
« Reply #5 on: 18 Nov 2021 09:27:21 am »
Correction .. OLEDB is installed on Content Manager and Dispatcher (App) servers.

Offline MFGF

  • Never knowingly correct
  • Super Moderator
  • Statesman
  • ******
  • Join Date: Jul 2005
  • Posts: 11,376
  • Forum Citizenship: +665/-10
  • Cognos Software Muppet
Re: Connect Cognos to SSAS Cube
« Reply #6 on: 22 Nov 2021 07:56:49 am »
Hi,

As your connection is a Data Server connection, there's really no point testing it in the Admin Console - this will test using both Compatible and Dynamic, and (as you are seeing) the Compatible test fails, as the connection is not Compatible-query. I would stay out of the Admin Console altogether and test just via the Data Server dialogs to avoid confusion.

A quick sanity check. When you are creating a new FM model,  and you are in the New Project dialog box (where you specify a name and location for the project), are you definitely checking the "Use Dynamic Query Mode" check box?

Cheers!

MF.
Meep!

Offline cognostechie

  • Statesman
  • ******
  • Join Date: Jul 2005
  • Posts: 1,672
  • Forum Citizenship: +133/-7
    • Pervisol
Re: Connect Cognos to SSAS Cube
« Reply #7 on: 22 Nov 2021 07:40:55 pm »
Yes, absolutetly. In fact, when you make a new FM model, that check box is selected by default and I just keep it that way.
Testing from the Data Server window tests successfully.

We have opened a ticket with IBM, let's see what they say.

Offline cognostechie

  • Statesman
  • ******
  • Join Date: Jul 2005
  • Posts: 1,672
  • Forum Citizenship: +133/-7
    • Pervisol
Re: Connect Cognos to SSAS Cube
« Reply #8 on: 29 Nov 2021 03:02:00 pm »
It turns out that some consultant made a FM Model using the SSAS Cube and published the package. When I open the model and publish the package again, it works fine and I can see the data in the report. However, I don't see any Data Source in the model but it still publishes the package ! I am wondering is there a different way to make a FM Model for SSAS cube? When we make a model for SSAS Cube, don't we have to select a Data Source? 

I can't upload an attachment. It says 'cannot access the location'.

Basically, there is + sign in the namespaces and package because there are objects inside them but there is no + sign in the Data Source !