Author Topic: Refresh Schemas, deployment and pass-through SQL :  (Read 834 times)

Offline Nimirod

  • Full Member
  • ***
  • Join Date: Oct 2013
  • Posts: 31
  • Forum Citizenship: +0/-0
Refresh Schemas, deployment and pass-through SQL :
« on: 06 Mar 2020 03:41:52 am »
We are working with Data Modules and we have some remarks/questions that we would like to share with the gurus’ community, and if you can give us some feedback, I would be grateful:
  • Is not easy to deploy Data Modules in a typical production workflow in a big enterprise (DEV, UAT, PRE-PROD, PROD), because schemas need to be refreshed for all connections and as we have thousands of objects (tables, views, synonyms, etc.), we prefer to select only the tables we need in the Data Module. Some important issues here :
    • Cognos automatically selects all new objects in the connection and as our system is huge we need to go checking (or unchecking) every single object for each of our servers.
    • As some connections are shared between some Data Modules and between some developers, we need to be very careful to do not uncheck objects that are used by others. For massive reporting and dashboarding we consider that this could become not easy to manage in a near future.
  • In order to avoid the point 1), we discovered that “Create table using SQL” with the “SQL Pass-through” option in Data Modules, allows us to execute queries without refreshing all the objects schema connections (only one object refresh is enougth). This way really simplify the 2Prod workflow and it help us to be more autonomous from Administrator role. We would like to generalize this way of doing but :
    • It Is not really documented by IBM, and we don’t know if it could be change by IBM in the future
    • What about performance, I did some tests and it doesn’t seem to be an issue but if someone have a better insight, thank you
  • Which option are you using for deplyoment task? could you share a bit of your own experience ? Are you having trouble with deplyoment tasks too ? Otherwise, do you see perhaps an issue with the procedure decribed in 2).
Sorry for being a bit verbose and thank you for the feedback.

Offline MFGF

  • Never knowingly correct
  • Super Moderator
  • Statesman
  • ******
  • Join Date: Jul 2005
  • Posts: 11,086
  • Forum Citizenship: +657/-10
  • Cognos Software Muppet
Re: Refresh Schemas, deployment and pass-through SQL :
« Reply #1 on: 06 Mar 2020 12:13:59 pm »
We are working with Data Modules and we have some remarks/questions that we would like to share with the gurus’ community, and if you can give us some feedback, I would be grateful:
  • Is not easy to deploy Data Modules in a typical production workflow in a big enterprise (DEV, UAT, PRE-PROD, PROD), because schemas need to be refreshed for all connections and as we have thousands of objects (tables, views, synonyms, etc.), we prefer to select only the tables we need in the Data Module. Some important issues here :
    • Cognos automatically selects all new objects in the connection and as our system is huge we need to go checking (or unchecking) every single object for each of our servers.
    • As some connections are shared between some Data Modules and between some developers, we need to be very careful to do not uncheck objects that are used by others. For massive reporting and dashboarding we consider that this could become not easy to manage in a near future.
  • In order to avoid the point 1), we discovered that “Create table using SQL” with the “SQL Pass-through” option in Data Modules, allows us to execute queries without refreshing all the objects schema connections (only one object refresh is enougth). This way really simplify the 2Prod workflow and it help us to be more autonomous from Administrator role. We would like to generalize this way of doing but :
    • It Is not really documented by IBM, and we don’t know if it could be change by IBM in the future
    • What about performance, I did some tests and it doesn’t seem to be an issue but if someone have a better insight, thank you
  • Which option are you using for deplyoment task? could you share a bit of your own experience ? Are you having trouble with deplyoment tasks too ? Otherwise, do you see perhaps an issue with the procedure decribed in 2).
Sorry for being a bit verbose and thank you for the feedback.

Hi,

I have been on a couple of calls recently with IBM product management, and they are keenly aware of the current schema limitations within data modules. The message that was given is that they are working on improving things in this area. Obviously no timescales were promised, but if I were you I'd try to hold off on implementing until you know more. I fully appreciate this might not be possible, but wanted to put this out there in case you hadn't heard this messaging.

Cheers!

MF.
Meep!

Offline Andrei I

  • Community Leader
  • *****
  • Join Date: Jan 2006
  • Posts: 193
  • Forum Citizenship: +7/-0
Re: Refresh Schemas, deployment and pass-through SQL :
« Reply #2 on: 06 Mar 2020 01:01:53 pm »
Cognos automatically selects all new objects in the connection and as our system is huge we need to go checking (or unchecking) every single object for each of our servers.

I wonder if you can control DB objects visibility by limiting access for the Cognos DB login using DB security?
Then (I am assuming)  any DB refresh should be faster...

Offline Nimirod

  • Full Member
  • ***
  • Join Date: Oct 2013
  • Posts: 31
  • Forum Citizenship: +0/-0
Re: Refresh Schemas, deployment and pass-through SQL :
« Reply #3 on: 09 Mar 2020 02:00:22 am »
The message that was given is that they are working on improving things in this area. Obviously no timescales were promised, but if I were you I'd try to hold off on implementing until you know more. I fully appreciate this might not be possible, but wanted to put this out there in case you hadn't heard this messaging.

@MGMF Thank you very much for all your insights and for your given time.

I should said that these are not the best news for us and I hope IBM will take care when enhancing this tool because we have done lots of development with it.

I think I will open a PMR to notice IBM that we are concerned by the limitations/enhancements of this tool.

Anyway, thank you for your input.

Best regards

Nr

Offline Nimirod

  • Full Member
  • ***
  • Join Date: Oct 2013
  • Posts: 31
  • Forum Citizenship: +0/-0
Re: Refresh Schemas, deployment and pass-through SQL :
« Reply #4 on: 09 Mar 2020 02:13:14 am »
I wonder if you can control DB objects visibility by limiting access for the Cognos DB login using DB security?

Hello Andrei,

We would prefer not change our DB system policy for this new tool while the tool is not yet stable and changes may happen in the future.

Thank you anyway for your advice.

Nr