Import SQL server data into a cognos report

[jschroeder]

I'm very new to cognos and just did a ready to run install on my machine.  I am trying to figure out how to imprt data from SQL server into a report. Are there any resources available to help learn how to do this?

[bdbits]

You don't really import the data, you create a report based on a package built with a Framework Manager model. When you run the report, it sends a query to the database which gives you the results of the query.

So you'll need to consult the Framework Manager user guide to learn how to create a package.

[jschroeder]

Thanks for the reply.  When I try to run the framework manager and create a new project, I get the error:

BMT-MD-003 CCL-BIT-0005 A socket reported a communication error.
<errorMessage> CAM_connect_0xffffffff the 'the gsk_environment_init' failed with the error code '202' </errorMessage>
<errorcode> 2113929065</errorcode>

[bus_pass_man]

There's a thread about that on Cognoise.
http://www.cognoise.com/index.php?topic=28333.0

There's some suggestions you might want to try.

Unfortunately, it isn't clear what the root cause of the reported problems were and how the problems were resolved so it is possible that there might be other approaches that might be useful.

I found these as well. 

http://www-01.ibm.com/support/docview.wss?uid=swg21341689

http://www-01.ibm.com/support/docview.wss?uid=swg21364221

[jschroeder]

Thanks, I've already seen all those. 
I'm thinking there's an issue with the framework install.
When I do the install it says there were issues and to check the logs.
I'm not sure where to find the FM install log files.  Any idea
also, if I try to restart from the config manager after the FM has been installed, it will not restart.

[bus_pass_man]

The instlog directory should have the installation log files.

Do you recall more about the message which was displayed after the installation completed?

also, if I try to restart from the config manager after the FM has been installed, it will not restart.

Not sure what you're trying to say.  It being?  Please don't tell me that you've installed FM to the same location as the cognos server.

CA has its own modeling tool built in.

As for resources for learning, there's a ton of PDFs on the IBM site.  These might be helpful.

Data Modeling Guide
Guidelines for Modeling Metadata Guide 
Dynamic Query Guide 
Dynamic Query Analyzer User Guide

IBM offers courses including a certification process.  Some one in your company might be able to point you to that.   Someone might be able mentor you. 

If your shop is just starting Cognos IBM has pre- and post-sales consultants to help customers get started.

[jschroeder]

It just says "There was a problem.  Please check install logs"   No other detail.  Why should I not install FM on the same server?  What is the name of the log file?  Do you have any specific links to the files you mention.  There's no one to mentor me.  We are all new to CA.  Management gave me the install files and said "get it working".

Edit:  found the log file.  Here are the 6 warnings it recorded:



24785 Successes
6 Warnings
0 NonFatalErrors
0 FatalErrors



Copy File:                Destination:
                          Status: WARNING
                          Additional Notes: WARNING - Source C:\Program Files (x86)\ibm\cognos\model\configuration\cogstartup.xml does not exist.

Copy File:                Destination:
                          Status: WARNING
                          Additional Notes: WARNING - Source C:\Program Files (x86)\ibm\cognos\model\configuration\coglocale.xml does not exist.

Copy Folder:              Destination: C:\Program Files (x86)\ibm\cognos\model\
                          Status: WARNING
                          Additional Notes: WARNING - There was a problem copying C:\Program Files (x86)\ibm\cognos\model\maps



Copy File:                Destination: C:\Program Files (x86)\ibm\cognos\model\configuration\cogstartup.xml.bkp
                          Status: WARNING
                          Additional Notes: WARNING - Source C:\Program Files (x86)\ibm\cognos\model\configuration\cogstartup.xml.bkp does not exist.



Copy File:                Destination: C:\Program Files (x86)\ibm\cognos\model\configuration\coglocale.xml.bkp
                          Status: WARNING
                          Additional Notes: WARNING - Source C:\Program Files (x86)\ibm\cognos\model\configuration\coglocale.xml.bkp does not exist.



Copy Folder:              Destination: C:\Program Files (x86)\ibm\cognos\model\
                          Status: WARNING
                          Additional Notes: WARNING - There was a problem copying C:\Program Files (x86)\ibm\cognos\model\maps.bkp

[bus_pass_man]

FM is a 32 bit app.  The server is 64 bit.  They share components but the shared components are either 32bit or 64 bit and bad things happen.

Do you have any specific links to the files you mention.

Yeah. Go into the instlog directory. 

Management gave me the install files and said "get it working".

That's a rather nebulous mandate.

I was going to say,

                What, you're expected to do all the admin of the server, set up the dev, test, and production environments, do the business analysis stuff, do the modeling, create the reports, do the testing of the models and the reports?  And oh yeah, get it done in two weeks because that's the number your manager pulled out of his or her ar5e?  My sympathies me old trout.  Did he say anything along the lines of 'having a bias to action'?

but changed my mind.

You want my advice? Get Cognos training.  The entire project of implementing Cognos applications is a multi-person, multi-disciple task.

Good luck.   :)

[jschroeder]

Per your advice, I installed the FM on a separate box and configured the gateway and Dispatcher URI to the original, and I'm now able to start FM without the error I was getting.  Successfully created a new project and a SQL server data source.   Not sure what the next steps are, but at least I'm inching along in what seems to be a positive direction.

[bus_pass_man]

That's good news.  I was about to mention that I had forgotten to ask you to uninstall and re-install.

The next step would be open your project in FM, import your metadata, and create a model.  There's lots of stuff you need to know such as the modeling paradigm that Cognos teaches but that's a big leap just in itself.  The modeling exercise is, in turn, a byproduct of the exercise of business analysis.  You need to have a vague idea about what the data warehouse is representing and what sorts of things the users -- both your immediate users-- the report authors-- and your ultimate users - the report consumers-- need. The report authors need to have similar understandings.  That also will guide the modeling efforts.  Then there's stuff like ETL. 

Which gets back to: what exactly are the expectations of your betters?

[jschroeder]

I have a lot of experience with the Microsoft BI stack, ETL, data warehouse design, Dimensional modeling, OLAP cubes. 
They want me to take on Cognos.  First step is to learn how to install it.  Then learn to create some  reports. 
I've been tasked with installing it on one of our servers.  Figured I'd start with a sandbox environment.  So I did a ready to run install on my local box to begin with.
I need to graduate to doing the install on the server.

[bdbits]

You will find Cognos is a much more integrated BI stack. I think it is overall a much better solution for an organization of more than a few users. It is easier to ensure your user base has one version of the truth, so to speak. Reporting is light years better than SSRS in my opinion. You also avoid the burgeoning population of Excel pivot tables the MS stack seems to encourage.

You will definitely want to learn FM thoroughly. The success of your BI projects is highly impacted by how well you model your data warehouse(s). The user guide is actually useful, especially if you have some background in BI as it seems you do. I recommend you browse through that to get a jump start. A class or two would be highly beneficial, but I know that is not always able to happen and have been in the "get it working" place more times than I care to recall. Having the BI background will be helpful.

While you can put FM on the same server, you have to install them to separate folders. I would not recommend installing it on the server, as I like servers to be servers and not used for desktop-style applications. Sounds like you got this sorted out, anyway. bus_pass_man is right in that IBM should be able to provide you some technical resources to help get started. When you do go to build a production server, you will probably want to get their input on sizing and configuration for your particular environment and user base.

For cubes, there are two technologies. The newer one I unfortunately never really have had an opportunity to use - Dynamic Cubes. This is preferable to the older tool, called Transformer. They are both different than SSAS, but of course both being cubes there are similarities. SSAS cubes are a bit more flexible, whereas Cognos requires a more rigid adherence to a dimensional structure, especially at the dimension level. You pretty have to explicitly use hierarchies, which the SSAS tools make you believe you do not even though underneath the result is just as structured, just more implicit. You can make SSAS cubes available as data sources within Cognos as well, but it is more-or-less a pass-through presentation, and you cannot for instance add metadata.

It will be an adjustment, but I think you will see Cognos has a lot going for it. It would be interesting to talk with you in a few months when you've had time to absorb The Cognos Way of doing things. :)  Good luck!

[kc9400]

FM is a 32 bit app.  The server is 64 bit.  They share components but the shared components are either 32bit or 64 bit and bad things happen.
Yeah. Go into the instlog directory. 


That's a rather nebulous mandate.

I was going to say,

                What, you're expected to do all the admin of the server, set up the dev, test, and production environments, do the business analysis stuff, do the modeling, create the reports, do the testing of the models and the reports?  And oh yeah, get it done in two weeks because that's the number your manager pulled out of his or her ar5e?  My sympathies me old trout.  Did he say anything along the lines of 'having a bias to action'?

but changed my mind.

You want my advice? Get Cognos training.  The entire project of implementing Cognos applications is a multi-person, multi-disciple task.

Good luck.   :)

HAHAHAHA!

I laugh but I've got to say it is certainly doable. Cognoise has helped me greatly, among other sources.

But yes, it's certainly more work than some people think it to be.

[MFGF]

I have a lot of experience with the Microsoft BI stack, ETL, data warehouse design, Dimensional modeling, OLAP cubes. 
They want me to take on Cognos.  First step is to learn how to install it.  Then learn to create some  reports. 
I've been tasked with installing it on one of our servers.  Figured I'd start with a sandbox environment.  So I did a ready to run install on my local box to begin with.
I need to graduate to doing the install on the server.

Looks like you're based in Colorado, USA? Do you work for an IBM business partner or are you an IBM client? If the former, I think IBM have a Partner Enablement team in the US, and it would be worth reaching out to them for some initial help to get started. If you're a client, did you buy direct from IBM or via an IBM business partner? If direct, you might get some help via your account manager, and if from a BP, then they might be willing to help you get started...

Cheers!

MF.