Usage of Determinants in using BLOBs in FM

[Kanag]

Hi Guys,
             Can u pls tell me how Determinants are useful for the usage of BLOBs/CLOBs in Framework Manager?

   I have seen from the google that Determinants can b used in case of BLOBs/CLOBs in FM...,

My requirement is :
           I have documents (pdf,word files) stored as BLOBs in Oracle Database... I need to have a cognos report with links to open those files...

Any help is much appreciated!!

Thanks,
KR

[blom0344]

What did Google say?

[Kavin]

Hi Blom,
              I do need some clarifications on the same...

ur response would help me a lot!

Thanks,
Kavin

[MFGF]

I am struggling to see how determinants could be relevant to the use of BLOB or CLOB datatypes.  Like Ties, I am curious to understand what you have uncovered that leads you to believe this is the case?

MF.

[Arpitagrawal9]

As per my knowledge its not advisable to use Blob data types.

To solve your problem what you can do is you can store your media files like pdf,word  in a centralized repository.And the in the data base table you can store this information in the form of Url as I did for one of my client.
So when you will import this table/view in your model you can directly create reports which would fetch these rows and users can click on the hyperlink to access the media file.

Hope this helps :)

[cognostechie]

I am struggling to see how determinants could be relevant to the use of BLOB or CLOB datatypes.  Like Ties, I am curious to understand what you have uncovered that leads you to believe this is the case?

MF.

BLOB and CLOB types cannot be used for joining to anything in FM. That's why it is suggested to have another key which you can used for joining. That key should be set as an unique determinant.

[Lynn]

Not 100% certain, but the determinant would be needed only in a case where there is no unique index defined for the table containing the BLOB.

Personally it just seems easier to me to have a table with URL/path information that points to a file stored on a server somewhere. Then just present the hyperlink in the report.

[cognostechie]

Determinants do not act as an 'alternative' in Cognos for the definition of data/structure in the database. Determinants are a way to tell Cognos how to generate the SQL. So if there is aunique index defined on a table using a key column, the determinant still should be set in the Query subject. That is the purpose of the determinant.

Even if you don't set the determinant, the data in the report could still be correct but in some cases, it can be wrong. I remember we have had an elaborate discussion regarding this. The classes in Cognos describe the use and neccecity of determinants. According to what they say and what we found in out tests, determinants are there to ensure the correct rollup of data at all levels of granularity, not just one instance.

Sometimes we get misled if one of our reports show correct data so we presume what we did is 'working'. Determinants are mandatory in case of conformed dimensions and BLOB/CLOB data types.

Other ways of presenting BLOB/CLOB types are definately welcome but this discussion is about whether or not to set the determinants.

[blom0344]

Not 100% certain, but the determinant would be needed only in a case where there is no unique index defined for the table containing the BLOB.

Personally it just seems easier to me to have a table with URL/path information that points to a file stored on a server somewhere. Then just present the hyperlink in the report.

Make that pretty much 100% sure. When you use a BLOB in a report then you will find that Cognos automatically adds the dataitem representing the unique (often primary) key of the table involved.

[Lynn]

Code Select Expand


There are less common cases when you need to use determinants:
● You want to uniquely identify the row of data when retrieving text BLOB data from the data
source.
Querying blobs requires additional key or index type information. If this information is not
present in the data source, you can add it using determinants. Override the determinants
imported from the data source that conflict with relationships created for reporting.
You cannot use multiple-segment keys when the query subject accesses blob data. With summary
queries, blob data must be retrieved separately from the summary portion of the query. To do
this, you need a key that uniquely identifies the row and the key must not have multiple segments.


The above quote from the FM user guide is what made me think you don't necessarily need them unless the database does not have a unique index based on a single field. However, the determinant would come in automatically on import of the table if it did have such an index anyway.

[Kavin]

Hi Arpit,
            Can we have a Shared drive ( say.., L: drive) for storing those files?.....
Will it work fine if we can have the shared drive path as a hyperlink in Cognos reports?

How would be the authentication in that case?.....Can Cognos sign-on authentication be used to access the shared folder?

Your thoughts on this is highly appreciated!

Thanks,
Kavin

[Arpitagrawal9]

You can have a server where you will store the files,Hence each file have a separate URL.
When you create a table in the Warehouse you will have a column named URL which will contain adrress of the media files(*.jpg,*.avi,*mp3).
So when you import this table into FM,you can directly publish the package and include the URL in the report Query so if you will create a list report it will have the Hyperlinks as rows which are directly coming from db,No need to deploy any thing in the Cognos Server.

Sample SQL:
CREATE TABLE [dbo].[LU_URL](
   [MediaID] [int] NOT NULL,
   [MediaName] [varchar](100),
   [URL] [varchar](255)

Output
MediaID   Filename                                                                               URL
1               A                         http://www.trulygraphics.com/wp-content/uploads/2010/12/thanks-for-the-add-woman.gif
2               B                         http://images.hotprofileplus.com/myspace-graphics/images_db/334/Thanks-for-the-add.jpg

Do let me know in case you face any issues.

Hope this helps  :)

[Kanag]

Thanks guys!

Will keep you posted if I need any help!

Thanks,
KR