How to only display fields in FM that have data

[Aamir]

Hi Everyone
We have a very big model with about 120 tables and all the fields in those tables are not always populated( they are just their for future use). We give our clients FM model who use our data warehouse and populate with their own data. We are trying to figure out how we can only display Query items in the Query subject that have data. We cannot use selected fields when creating query subject b'cos different clients populate different fields.
Ex:
             Field1         Field2         Field3   
Client A:  Yes            Yes            No
Client B:   No             Yes            Yes

Yes: Data present
No: Data not populated.

Any thoughts and suggestions greatly appreciated

[blom0344]

120 tables? Surely that is a medium sized FM  :)

At the moment it is beyond me WHY you would need this, but by using stored procedures and dynamic SQL you 'COULD' build an intermediate layer in the database that consists of views on the tables with ONLY those fields that contain at least one value other than null.
(A bit like building a 1:10 Titanic model from matchsticks  :)  )

Trouble is that you cannot evaluate the Cognos model as the model query subjects will parse the database objects and find no reference. It will also lead to error messages from the frontend.

Cognos does not facilitate this type of dynamic rendering unless you find a way to purge the model.xml from the query items and rebuild the package for each customer..

[Aamir]

Thanks for the reply. Yeah thats true our Medium size model has 120 tables and 1055 query subjects and more than 60,000 query items. The important thing is it meets ALL our clients needs. The reason we are looking to supress these fields is, for example a table has 260 fields (we need all these fields to match some federal standards) and some of our clients think they don't need certain fields and other need them more then thier lives. While building reports its very tiresome to search all those 260 query items so we are trying to supress some fields which are not being used. As those fields are not consistent from client to client we are looking for some dynamic solution where we can hide/eliminate the fields that are not populated. To the best of our knowledge I know its not possible to do it dynamically but I thought there might be some "Genie"  in the forum who can provide me the solution. If we had only one client or clients who populate the colums consistently we could use a select statement while creating the query subject and selecting only those columns that are populated. So this is the whole story in short.

[blom0344]

Okay ,that is a pretty clear story. One approach is to scan - depending on the RDBMS - the DBA views to establish those tablefields that contain no data. An exhausting exercise, but possible with a stored procedure and dynamic SQL.
It will at least give you an insight for a given customer which fields can be considered 'superfluous'.
And , 60000 query items is quite a lot!!

Scan for a specific table would be something like - For SQL server 2005 - :

Code Select Expand


CREATE PROCEDURE [dbo].[pr_count_distinct_column_values]
@srcTable varchar(100)    -- source table
AS
  BEGIN
    SET nocount on

--DECLARE VARIABLES
DECLARE @Query varchar(4000)
DECLARE @srcTable1 varchar(100)
SELECT @srcTable1 = @srcTable


-- CURSOR STATEMENT
DECLARE LQueries CURSOR LOCAL FAST_FORWARD
FOR SELECT
'INSERT INTO [DBO].[REF_DISTINCT_COLUMN_VALUES]' +
N'SELECT' +
QUOTENAME(TABLE_NAME,'''') + ',' +
QUOTENAME(COLUMN_NAME,'''') + ',' +
    N'COUNT(DISTINCT T.' + QUOTENAME(COLUMN_NAME) +N')' + N' FROM ' + N'' + '[dbo]' + '.' +
    QUOTENAME(TABLE_NAME) + ' T'
FROM SOMEDATABASE.INFORMATION_SCHEMA.COLUMNS
WHERE
        SOMEDATABASE.INFORMATION_SCHEMA.COLUMNS.TABLE_NAME = @srcTable1

-- Read load query
OPEN LQueries
-- Loop through
FETCH NEXT FROM LQueries INTO @LQuery
WHILE @@FETCH_STATUS = 0
BEGIN
    --execute statement
    EXEC(@LQuery)
    --assign the next load statement to our @LoadStatement variable
    FETCH NEXT FROM LQueries INTO @LQuery
END
-- close our cursor
CLOSE LQueries
-- and free up the resources
DEALLOCATE LQueries

SET nocount off
  END



[Aamir]

Thanks Blom for the update. This is really helpful. our warehouse Team is not comfortable to do scan on database because it will create huge maintenance problem. I'm testing ur script in my local enviornment and looking at performance too. We are looking if we can do anything using SDK, and also submitted a enhancement request to Cognos(I know u might be laughing at it....cos the usual answer from them is no).
Once again thanks for all the inputs.