Below is a screenshot of my crosstab
Wednesday 25 January 2012
Tuesday 24 January 2012
MDX Functions in Report studio
Hi Guys.
Now i have only included screenshot.I will include with syntax and with a practice example when i do them .Example screenshots taken fron Slideshare.Nice presentation
http://www.slideshare.net/PerformanceG2/pg2-multi-dimensional-reporting-using-report-studio
Current Measure
Tuple
Sets
Some Interesting time function in MDX
AS OF DATE
[sales_and_marketing].[Time].[Time].[Month]->:[PC].[@MEMBER].[20040101-20040131]
when you go for cube this value automatically gets stored
:[PC].[@MEMBER].[20040101-20040131] value is cube value for [2004Jan] this is how cube finds it .So if you put [2004] there directly it all works fine. when prompt takes [2004jan] it is actually taking this value.This is member unique name.
How last period is used as filter for Months
lastPeriods(13,[sales_and_marketing].[Time].[Time].[Month]->?P_Month?)
This will generate the last 13 periods.that is gets previous 13 members.Good for previous year.
Note - Using lead ,Lag will return you only one member and Not 13 members.So they cant be used here.
Except
IBM Defination
except ( set_expression1 , set_expression2 [ , all ] )
Returns the members of "set_expression1" that are not also in "set_expression2". Duplicates are retained only if the optional keyword all is supplied as the third argument.
How it can be used in report
If you want to show data from May 2005 to Nov 2006.Below can be used
except( periodsToDate([sales_and_marketing].[Time].[Time].[Time] , [endDate] ) , periodsToDate([sales_and_marketing].[Time].[Time].[Time] , prevMember( [beginDate] ) ) )
The first thing gives all month till end date Jan 2004 to Dec 2007, second thing all month till one before begin date Jan 2004 to April 2005.So once you do extract you are left with May.Here you could not have used lastperiod directly .May be with use of months_between.
Check out below link for some good Idea on how to use functions with Time
http://www.ibm.com/developerworks/data/library/cognos/reporting/dimensional_queries/page561.html
Generate
A common example of generate is for generating top 2 products of product line by revenue.You will find it in the MDX help guide.But we hardly come across such scenario.
IBM Defination.
generate ( set_expression1 , set_expression2 [ , all ] )
Evaluates "set_expression2" for each member of "set_expression1" and joins the resulting sets by union. The result retains duplicates only when the optional keyword "all" is supplied as the third argument.
generate ( [Product line] , topCount ( descendants ( currentMember ( [great_outdoors_company].[Products].[Products] ) , [great_outdoors_company].[Products].[Products].[Product name] ) , 2 , [Revenue] ) )
Result: Returns the top two products by revenue for each product line
We have product line and below it level product type.Here we give product line and it takes out product type for each product line and finds the top2 from product type.
How you can use it in report.
You can use number of ideas to achieve the below.Its just one example of generate.Need to show selected year and year prior to that.
[SelectedMonths] -- data item.
set([sales_and_marketing].[Time].[Time].[Month]->?Parameter1?)
There are few function that work only on set and not on member.Remember that members in hierarchy make a set . So the set that has been done here is for use in generate
generate([SelectedMonths] , lag( currentMember([sales_and_marketing].[Time].[Time]) , 12) )
This is done so that both the current and previous year come together.
union( [PreviousMonths] , [SelectedMonths] ))
order( [UnionOfSelectedSets] , [PositionOfMonthInYear] )
------------------------------------------------------------------------------------------
_firstFromSet([great_outdoors_company].[Products].[Products].[Product line],2,2)
_firstFromSet([great_outdoors_company].[Products].[Products].[Product line],2,8)
The product line has five members .firstfromset( a,max members,overflow).If the total
members of set come within the overflow and max taht is 4 then it will show all otherwise
it will show only 2 . since there are 5 members it shows only 2
Hierarchy
hierarchy ([Camping Equipment])
hierarchy ([great_outdoors_company].[Products].[Products].[Product line])
--- when you do this every member of hierarchy is taken.Consideryou take product line then every member of hierarchy till product will be shown.It find the member in the hierarchy and shows the entire hierarchy it belongs to.Both the expression return the same result
level ([Camping Equipment] )
Retuns members of that level.Here you have put product line so only member of product line will
be displayed.There are 5 product lines her
Interesting Function
---------------------------------------------------------------------------------------
member(total(currentMeasure within set [B Product List]),'BProducts','B Products',
[great_outdoors_company].[Products].[Products]) .
member(total([great_outdoors_company].[Measures].[Quantity sold] within set
[2005]),'Quantity2005','Quantity sold 2005')
member(total([great_outdoors_company].[Measures].[Revenue] within set
[2005]),'Revenue2005','Revenue 2005')
Defines a member based on "value_expression" in "hierarchy". "String1" identifies the member
created by this function. It must be unique in the query and different from any other member in
the same hierarchy. "String2" is the caption of the member; if it is absent, the caption is
empty. To ensure predictable results, it is recommended that you supply the "hierarchy".
----------------------------------------------------------------------------------------------[
How to get B list
filter([great_outdoors_company].[Products].[Products].[Product name],caption
([great_outdoors_company].[Products].[Products].[Product name]) starts with 'B')
filter ( [Product line] , [Gross margin] > .30 )
filter ( set_expression , Boolean_expression )
Returns the set resulting from filtering a specified set based on the Boolean condition. Each
member is included in the result if and only if the corresponding value of "Boolean_expression"
is true.
-------------------------------------------------------------------------
prevMember ( member )
nextMember ( member )
lag([2006/May],6)
lead([2006/May],6)
Lead ,Lag allows you to specify the number of positions that you want to move forward or backward
---------------------------------------------------------------------------
ancestor ( member, level|integer )
Returns the ancestor of "member" at "level" or at "integer" number of levels above "member".
Example: ancestor ( [TrailChef Water Bag] , [great_outdoors_company].[Products].[Products].
[Product type] )
Result: Cooking Gear
Example: ancestor ( [TrailChef Water Bag] , 1 )
Result: Cooking Gear
ancestor ( member, level|integer )
parent ( member|measure )
cousin ( member1 , member2 )
Returns the child member of "member2" with the same relative position as "member1" to its parent.
children ( member )
descendants ( member|set_expression , level|distance [ , { self|before|beforewithmember|after } ]
)
descendants allow you to specify the members at a level or at a distance including all the
members till the level specified or only the members of level specified
firstChild ( [By Product Lines] )
rootmember can be used to [By Product Lines]
lastChild ( member )
siblings ( member )
Returns the children of the parent of the specified member.
lastSibling ( member ) -------Similar is closing period
firstSibling ( member ) ------- openingPeriod ( level [ , member ] )
lastPeriods(2,[2005]) ---- from member specified back 2 period
periodsToDate ( level , member ) --- from start till member specified
total([Revenue] within set periodsToDate([great_outdoors_company].[Years].[Years].
[Year],currentMember([great_outdoors_company].[Years].[Years])))
similar is item ( set_expression , index )
Returns a member from the "index" location within "set_expression". The index into the set is
zero based.
item ( children ( [Camping Equipment] ) , 2 ) -- item returns only one member
head ( set_expression [ , index_expression ] ) -- Head returns multiple members
tail ( set_expression [ , index_expression ] )
Returns the last "index_expression" elements of "set expression". The default for
"index_expression" is 1.
Difference between tail and lastperiod is you can specify where last period start.It can actually
start from middle but tail always starts from last member
-----------------------------------------------------------------------
If you want to move up use
Parent -- only one level up
ancestor -- allows you to specify level up 1,2,3,4... etc
If you want to move horizontal
Lead
Lag
Nextmember
Previousmember
If you want to move down use
children -- only one level
descendant -- you can specify levels 1,2,3,...etc
------------------------------------------------------------
Very easy to simulate parallelperiod. Use ancestor go on top and use lead lag to move
Now i have only included screenshot.I will include with syntax and with a practice example when i do them .Example screenshots taken fron Slideshare.Nice presentation
http://www.slideshare.net/PerformanceG2/pg2-multi-dimensional-reporting-using-report-studio
In case of SQL the
hierarchy can be represented by Self referrential joins or by Flattening the
hierarchy. In case of Flat hierarchy we can add columns names like Country,
State,City and drill up and down a level . In case of Cubes we do not have
multiple columns so we need to know functions to navigate the hierarchy
If you want to move up use
Parent -- only one
level up
ancestor -- allows
you to specify level up 1,2,3,4... etc
If you want to move horizontal
Lead
Lag
Nextmember
Previousmember
If you want to move down use
children -- only one
level
descendant -- you
can specify levels 1,2,3,...etc
Tuple
Sets
Some Interesting time function in MDX
AS OF DATE
[sales_and_marketing].[Time].[Time].[Month]->:[PC].[@MEMBER].[20040101-20040131]
when you go for cube this value automatically gets stored
:[PC].[@MEMBER].[20040101-20040131] value is cube value for [2004Jan] this is how cube finds it .So if you put [2004] there directly it all works fine. when prompt takes [2004jan] it is actually taking this value.This is member unique name.
How last period is used as filter for Months
lastPeriods(13,[sales_and_marketing].[Time].[Time].[Month]->?P_Month?)
This will generate the last 13 periods.that is gets previous 13 members.Good for previous year.
Note - Using lead ,Lag will return you only one member and Not 13 members.So they cant be used here.
Except
IBM Defination
except ( set_expression1 , set_expression2 [ , all ] )
Returns the members of "set_expression1" that are not also in "set_expression2". Duplicates are retained only if the optional keyword all is supplied as the third argument.
How it can be used in report
If you want to show data from May 2005 to Nov 2006.Below can be used
except( periodsToDate([sales_and_marketing].[Time].[Time].[Time] , [endDate] ) , periodsToDate([sales_and_marketing].[Time].[Time].[Time] , prevMember( [beginDate] ) ) )
The first thing gives all month till end date Jan 2004 to Dec 2007, second thing all month till one before begin date Jan 2004 to April 2005.So once you do extract you are left with May.Here you could not have used lastperiod directly .May be with use of months_between.
Check out below link for some good Idea on how to use functions with Time
http://www.ibm.com/developerworks/data/library/cognos/reporting/dimensional_queries/page561.html
Generate
A common example of generate is for generating top 2 products of product line by revenue.You will find it in the MDX help guide.But we hardly come across such scenario.
IBM Defination.
generate ( set_expression1 , set_expression2 [ , all ] )
Evaluates "set_expression2" for each member of "set_expression1" and joins the resulting sets by union. The result retains duplicates only when the optional keyword "all" is supplied as the third argument.
generate ( [Product line] , topCount ( descendants ( currentMember ( [great_outdoors_company].[Products].[Products] ) , [great_outdoors_company].[Products].[Products].[Product name] ) , 2 , [Revenue] ) )
Result: Returns the top two products by revenue for each product line
We have product line and below it level product type.Here we give product line and it takes out product type for each product line and finds the top2 from product type.
How you can use it in report.
You can use number of ideas to achieve the below.Its just one example of generate.Need to show selected year and year prior to that.
[SelectedMonths] -- data item.
set([sales_and_marketing].[Time].[Time].[Month]->?Parameter1?)
There are few function that work only on set and not on member.Remember that members in hierarchy make a set . So the set that has been done here is for use in generate
generate([SelectedMonths] , lag( currentMember([sales_and_marketing].[Time].[Time]) , 12) )
This is done so that both the current and previous year come together.
union( [PreviousMonths] , [SelectedMonths] ))
order( [UnionOfSelectedSets] , [PositionOfMonthInYear] )
------------------------------------------------------------------------------------------
_firstFromSet([great_outdoors_company].[Products].[Products].[Product line],2,2)
_firstFromSet([great_outdoors_company].[Products].[Products].[Product line],2,8)
The product line has five members .firstfromset( a,max members,overflow).If the total
members of set come within the overflow and max taht is 4 then it will show all otherwise
it will show only 2 . since there are 5 members it shows only 2
Hierarchy
hierarchy ([Camping Equipment])
hierarchy ([great_outdoors_company].[Products].[Products].[Product line])
--- when you do this every member of hierarchy is taken.Consideryou take product line then every member of hierarchy till product will be shown.It find the member in the hierarchy and shows the entire hierarchy it belongs to.Both the expression return the same result
level ([Camping Equipment] )
Retuns members of that level.Here you have put product line so only member of product line will
be displayed.There are 5 product lines her
Interesting Function
---------------------------------------------------------------------------------------
member(total(currentMeasure within set [B Product List]),'BProducts','B Products',
[great_outdoors_company].[Products].[Products]) .
member(total([great_outdoors_company].[Measures].[Quantity sold] within set
[2005]),'Quantity2005','Quantity sold 2005')
member(total([great_outdoors_company].[Measures].[Revenue] within set
[2005]),'Revenue2005','Revenue 2005')
Defines a member based on "value_expression" in "hierarchy". "String1" identifies the member
created by this function. It must be unique in the query and different from any other member in
the same hierarchy. "String2" is the caption of the member; if it is absent, the caption is
empty. To ensure predictable results, it is recommended that you supply the "hierarchy".
----------------------------------------------------------------------------------------------[
How to get B list
filter([great_outdoors_company].[Products].[Products].[Product name],caption
([great_outdoors_company].[Products].[Products].[Product name]) starts with 'B')
filter ( [Product line] , [Gross margin] > .30 )
filter ( set_expression , Boolean_expression )
Returns the set resulting from filtering a specified set based on the Boolean condition. Each
member is included in the result if and only if the corresponding value of "Boolean_expression"
is true.
-------------------------------------------------------------------------
prevMember ( member )
nextMember ( member )
lag([2006/May],6)
lead([2006/May],6)
Lead ,Lag allows you to specify the number of positions that you want to move forward or backward
---------------------------------------------------------------------------
ancestor ( member, level|integer )
Returns the ancestor of "member" at "level" or at "integer" number of levels above "member".
Example: ancestor ( [TrailChef Water Bag] , [great_outdoors_company].[Products].[Products].
[Product type] )
Result: Cooking Gear
Example: ancestor ( [TrailChef Water Bag] , 1 )
Result: Cooking Gear
ancestor ( member, level|integer )
parent ( member|measure )
cousin ( member1 , member2 )
Returns the child member of "member2" with the same relative position as "member1" to its parent.
children ( member )
descendants ( member|set_expression , level|distance [ , { self|before|beforewithmember|after } ]
)
descendants allow you to specify the members at a level or at a distance including all the
members till the level specified or only the members of level specified
firstChild ( [By Product Lines] )
rootmember can be used to [By Product Lines]
lastChild ( member )
siblings ( member )
Returns the children of the parent of the specified member.
lastSibling ( member ) -------Similar is closing period
firstSibling ( member ) ------- openingPeriod ( level [ , member ] )
lastPeriods(2,[2005]) ---- from member specified back 2 period
periodsToDate ( level , member ) --- from start till member specified
total([Revenue] within set periodsToDate([great_outdoors_company].[Years].[Years].
[Year],currentMember([great_outdoors_company].[Years].[Years])))
similar is item ( set_expression , index )
Returns a member from the "index" location within "set_expression". The index into the set is
zero based.
item ( children ( [Camping Equipment] ) , 2 ) -- item returns only one member
head ( set_expression [ , index_expression ] ) -- Head returns multiple members
tail ( set_expression [ , index_expression ] )
Returns the last "index_expression" elements of "set expression". The default for
"index_expression" is 1.
Difference between tail and lastperiod is you can specify where last period start.It can actually
start from middle but tail always starts from last member
-----------------------------------------------------------------------
If you want to move up use
Parent -- only one level up
ancestor -- allows you to specify level up 1,2,3,4... etc
If you want to move horizontal
Lead
Lag
Nextmember
Previousmember
If you want to move down use
children -- only one level
descendant -- you can specify levels 1,2,3,...etc
------------------------------------------------------------
Very easy to simulate parallelperiod. Use ancestor go on top and use lead lag to move
Within set ,Within detail, Within aggregate Explained
Hi Guys,
I was going through dimensional functions and i have found some preety interesting material on within set,within aggregate and within detail at the below link.
http://publib.boulder.ibm.com/infocenter/c8bi/v8r4m0/index.jsp?topic=/com.ibm.swg.im.cognos.ug_cr_rptstd.8.4.0.doc/ug_cr_rptstd_id10574cr_rptstd_wrkdat_agg_val_xtabs_char.html
So i tried that. However i am not sure how within aggregate works and i found out some new thing in cognos 10
However what i noticed is i am getting same values for both Within set and within aggregate.
Below is a new thing that i noticed in Cognos10.Dimensional Edge summary
Below is a case that i tried for Nested Measures
Consider the case when measures are nested on rows.Like you have Product number on rows and unit price and unit sales nested on row close to it.they are not provided as measures in measure section
Cognos earlier to 10 was taking total(currentmeasure within detail [dimension])
Now this is done assuming currentmeasure is your single measure that is there and dimension is the one that you are doing total on.But in case of nested measure this changes as you dont have a single measure but multiple ones.
total([Quantity] within detail [Product type])--- This will calculate the total of quantity for
each product type for each product line .That is it is to be position as shown in diagram
Below i a Image of Nested measures
Below is the total calculated by using Within set ,Within aggregate and within detail
I was going through dimensional functions and i have found some preety interesting material on within set,within aggregate and within detail at the below link.
http://publib.boulder.ibm.com/infocenter/c8bi/v8r4m0/index.jsp?topic=/com.ibm.swg.im.cognos.ug_cr_rptstd.8.4.0.doc/ug_cr_rptstd_id10574cr_rptstd_wrkdat_agg_val_xtabs_char.html
So i tried that. However i am not sure how within aggregate works and i found out some new thing in cognos 10
However what i noticed is i am getting same values for both Within set and within aggregate.
Below is a new thing that i noticed in Cognos10.Dimensional Edge summary
Below is a case that i tried for Nested Measures
Consider the case when measures are nested on rows.Like you have Product number on rows and unit price and unit sales nested on row close to it.they are not provided as measures in measure section
Cognos earlier to 10 was taking total(currentmeasure within detail [dimension])
Now this is done assuming currentmeasure is your single measure that is there and dimension is the one that you are doing total on.But in case of nested measure this changes as you dont have a single measure but multiple ones.
total([Quantity] within detail [Product type])--- This will calculate the total of quantity for
each product type for each product line .That is it is to be position as shown in diagram
Below i a Image of Nested measures
Below is the total calculated by using Within set ,Within aggregate and within detail
Monday 23 January 2012
Using Cube in report studio
Hi Guys ,
Thanks to people on It toolbox.I have collected some material on using cubes in report studio
the link below is great.It a slideshow but covers all and very good for a slideshow.
Special thanks to Winson who has been making Ittoolbox a great place to discuss cognos.This guys has been active since 2008 or 09 i guess.Thanks Winson.
http://www.slideshare.net/PerformanceG2/pg2-multi-dimensional-reporting-using-report-studio
So Main things to learn if you are new to Dimensional reporting are
1) Hierarchies
2)Levels
3)Members
4)Sets
Now you might be familier with first two if you have worked on dimensional model like me.
The Book Listed below is also great.If you know those differences and couple of functions i think you are set
http://www.it.northwestern.edu/bin/docs/Cognos_8_Best_Practices_vol_2.pdf
Below link has all the Cognos proven practices articles.All the PDF that you wanted about best
practices
http://www.ibm.com/developerworks/data/library/cognos/cognosprovenpractices.html
Thanks to people on It toolbox.I have collected some material on using cubes in report studio
the link below is great.It a slideshow but covers all and very good for a slideshow.
Special thanks to Winson who has been making Ittoolbox a great place to discuss cognos.This guys has been active since 2008 or 09 i guess.Thanks Winson.
http://www.slideshare.net/PerformanceG2/pg2-multi-dimensional-reporting-using-report-studio
So Main things to learn if you are new to Dimensional reporting are
1) Hierarchies
2)Levels
3)Members
4)Sets
Now you might be familier with first two if you have worked on dimensional model like me.
The Book Listed below is also great.If you know those differences and couple of functions i think you are set
http://www.it.northwestern.edu/bin/docs/Cognos_8_Best_Practices_vol_2.pdf
Below link has all the Cognos proven practices articles.All the PDF that you wanted about best
practices
http://www.ibm.com/developerworks/data/library/cognos/cognosprovenpractices.html
Saturday 21 January 2012
QE-DEF-0313 and CCL-RCI-0011 Error When using Package as Datasource
Hi Guys
QE-DEF-0313 and CCL-RCI-0011 Error
I am using Cognos10 FM and cognos 8.4 transformer that is the reason this error comes.I need to use a 8.4Fm to make it work .
Where to Findi cognos.ini file in cognos 8.4??
The file cognos.ini didn't exist in cognos 8.4 ,which replaced by cs7g.ini (...TransFormer\CS7Gateways\bin) for the same cs7g.ini file should contain the database footprints .
In my computer its installed on D drive and below is the cs7g.ini file content
Location]
Install Location=.\
Rendition Base Location=.\
[Services]
COGNLSTAB=.\coglang.tab
SRVCMSGS=.\srvcmsgs_en.msg
CTDBA=ctdba,ctdba15
[Databases]
great_outdoors_sales = ^User ID:^?Password:;LOCAL;OR;ORACLE@%s@orcl/%s@COLSEQ=
When using Package as data source i am not able to see any packages ??
I am using Transformer 10 and Cognos 10 and i was not able to see packages when i try to create package as datasource .Please note that the dispatcher URI for both Cognos 10 and Transformer configuration are slightly different.
Dispatcher URI for Transformer - http://localhost:9300/p2pd/servlet/dispatch
Notice that /ext is not there
Dispatcher URI for Cognos 10 applicaiton server - http://localhost:9300/p2pd/servlet/dispatch/ext
QE-DEF-0313 and CCL-RCI-0011 Error
I am using Cognos10 FM and cognos 8.4 transformer that is the reason this error comes.I need to use a 8.4Fm to make it work .
Where to Findi cognos.ini file in cognos 8.4??
The file cognos.ini didn't exist in cognos 8.4 ,which replaced by cs7g.ini (...TransFormer\CS7Gateways\bin) for the same cs7g.ini file should contain the database footprints .
In my computer its installed on D drive and below is the cs7g.ini file content
Location]
Install Location=.\
Rendition Base Location=.\
[Services]
COGNLSTAB=.\coglang.tab
SRVCMSGS=.\srvcmsgs_en.msg
CTDBA=ctdba,ctdba15
[Databases]
great_outdoors_sales = ^User ID:^?Password:;LOCAL;OR;ORACLE@%s@orcl/%s@COLSEQ=
When using Package as data source i am not able to see any packages ??
I am using Transformer 10 and Cognos 10 and i was not able to see packages when i try to create package as datasource .Please note that the dispatcher URI for both Cognos 10 and Transformer configuration are slightly different.
Dispatcher URI for Transformer - http://localhost:9300/p2pd/servlet/dispatch
Notice that /ext is not there
Dispatcher URI for Cognos 10 applicaiton server - http://localhost:9300/p2pd/servlet/dispatch/ext
IQD generated in FM and Joins
Hi
I have generated IQD for each individual table in my database layer in FM.My question is how will the joins treated as each IQD is showing a single table select.Its not showing any joins.
Even if i create IQD from my business layer where all the tables related to product are grouped together
the IQD will show joins for product and its related tables.The question is how transformer will come to know what is the join between product and sales ???
Ans - It will come to know the join based on column names.There should be a common column between both sales and product
make join between iqds you have to use the same column name. Let say you have two iqd :
One for customer containing :
Customer Id
Customer Name
...
One for Orders :
Order Id
Customer Id
Product Id
Amount
...
The join between these iqds will be "Customer Id". You can change the name of the column in the property tab of the field.
How will you solve issues like loop in tranformer ?Suppose product ,sales ,country form a loop.How will this be solved .
I have generated IQD for each individual table in my database layer in FM.My question is how will the joins treated as each IQD is showing a single table select.Its not showing any joins.
Even if i create IQD from my business layer where all the tables related to product are grouped together
the IQD will show joins for product and its related tables.The question is how transformer will come to know what is the join between product and sales ???
Ans - It will come to know the join based on column names.There should be a common column between both sales and product
make join between iqds you have to use the same column name. Let say you have two iqd :
One for customer containing :
Customer Id
Customer Name
...
One for Orders :
Order Id
Customer Id
Product Id
Amount
...
The join between these iqds will be "Customer Id". You can change the name of the column in the property tab of the field.
How will you solve issues like loop in tranformer ?Suppose product ,sales ,country form a loop.How will this be solved .
TR1008 Cognos.INI and Transformer 8.4 Issue
Hi Guys,
I was getting this error when i was trying to use my first IQD.Could not figure it out for long time below is the solution from IT toolbox
Hi folkz,
there is no Cognos.ini in Cognos 8 Transformer.
The file you search for is cs7g.ini.
[Location]
Install Location=.\
Rendition Base Location=.\
[Services]
COGNLSTAB=.\coglang.tab
SRVCMSGS=.\srvcmsgs_en.msg
CTDBA=ctdba,ctdba15
[Databases]
Datasource name=connection string
Below is my connection string.Datasource name you will get in IQD, or your FM or your congos admin
configuration.Connection string you will get when you go to adminstration and test the datasource
.Once you do this the transformer will ask you for userid and password of database
great_outdoors_sales=^User ID:^?Password:;LOCAL;OR;ORACLE@%s@orcl/%s@COLSEQ=
I was getting this error when i was trying to use my first IQD.Could not figure it out for long time below is the solution from IT toolbox
Hi folkz,
there is no Cognos.ini in Cognos 8 Transformer.
The file you search for is cs7g.ini.
[Location]
Install Location=.\
Rendition Base Location=.\
[Services]
COGNLSTAB=.\coglang.tab
SRVCMSGS=.\srvcmsgs_en.msg
CTDBA=ctdba,ctdba15
[Databases]
Datasource name=connection string
Below is my connection string.Datasource name you will get in IQD, or your FM or your congos admin
configuration.Connection string you will get when you go to adminstration and test the datasource
.Once you do this the transformer will ask you for userid and password of database
great_outdoors_sales=^User ID:^?Password:;LOCAL;OR;ORACLE@%s@orcl/%s@COLSEQ=
Using Cube in Report Studio Cognos10
Hi Guys,
New discovery with cubes !!!!!!!!!!
If you have worked with report studio you know that once you drag product name from product hierarchy into list.All the product names gets displayed.However if you do this on a package that is using cube as a source you will be surprised nothing happens.You need to use MDX function :-))
New discovery with cubes !!!!!!!!!!
If you have worked with report studio you know that once you drag product name from product hierarchy into list.All the product names gets displayed.However if you do this on a package that is using cube as a source you will be surprised nothing happens.You need to use MDX function :-))
Friday 20 January 2012
Cognos Transformer TR1100 invalid Keyword
Hi Guys,
Finally I found the Error
I was using Cognos10 samples that i had in cognos 8.4 transformer.Nothing else.Took me a long time
to understand this . But you might find my below analysis useful
This is my first time using a transformer.I have basic idea how it works and i have lot of experience in report studio and cognos administration.But first time using a transformer.
I have installed transformer on my machine and trying to open a .mdl file from cognos samples.However it is giving TR1100.
Below is a link about how to build a cube .
http://www.pwka.com/allegati/gare/pp_disc_trns.pdf
If you click on help you will get the below detailed error description
TR1100
The import file file_name has an invalid keyword on line n.
There is an invalid keyword in the MDL script.
Check that all the keywords are spelled correctly on the specified line. For more information about supported keywords, see the Transformer Developer Guide.
t.
Things to remember while dealing with Cubes
Your Cube model is .Mdl
Once you publish a cube it becomes .Mdc
I was not able to remove the error with .mdl file instead i used the .mdc file created a datasource connection in cognos .In cognos 10 no need to go to FM to publish the package.Once you create a datasource it will ask you whether you require to create a package and it will automatically create a package
Finally I found the Error
I was using Cognos10 samples that i had in cognos 8.4 transformer.Nothing else.Took me a long time
to understand this . But you might find my below analysis useful
This is my first time using a transformer.I have basic idea how it works and i have lot of experience in report studio and cognos administration.But first time using a transformer.
I have installed transformer on my machine and trying to open a .mdl file from cognos samples.However it is giving TR1100.
Below is a link about how to build a cube .
http://www.pwka.com/allegati/gare/pp_disc_trns.pdf
If you click on help you will get the below detailed error description
TR1100
The import file file_name has an invalid keyword on line n.
There is an invalid keyword in the MDL script.
Check that all the keywords are spelled correctly on the specified line. For more information about supported keywords, see the Transformer Developer Guide.
t.
Things to remember while dealing with Cubes
Your Cube model is .Mdl
Once you publish a cube it becomes .Mdc
I was not able to remove the error with .mdl file instead i used the .mdc file created a datasource connection in cognos .In cognos 10 no need to go to FM to publish the package.Once you create a datasource it will ask you whether you require to create a package and it will automatically create a package
Thursday 19 January 2012
Setting up Audit database for Cognos
Hi Guys ,
Have not tried this .Just putting here as thing to try .
Have not tried this .Just putting here as thing to try .
Customise login page to diplay annoucements
Hi Guys,
One thing that can be done is using the cognos login page to dispaly annoucements like cognos service will be down or any messages.So i am looking at how to do that .
If you want to customize login page then go on webserver you will find two .css files
1> ibm.css
2> styles.css
I have not tried this but will do so when i get some free time
One thing that can be done is using the cognos login page to dispaly annoucements like cognos service will be down or any messages.So i am looking at how to do that .
If you want to customize login page then go on webserver you will find two .css files
1> ibm.css
2> styles.css
I have not tried this but will do so when i get some free time
Friday 13 January 2012
Max and Min calculation side by side
Hi Guys ,
I dont know why i was thinking we cannot calculate Max and Min side by side.It took me a while to figure out.Below is the case
Use go sales ,sales namespace .Take Current year, date,Quantity ..filter current year to be 2004 .
1) try to take max(date for currentyear) and Min(date) for currentyear you will see that you are getting Jan 1 2004 and Jan 31 2004 .Which is as what you expect.
Now put quantity next to it.You will see that you get 0 for max quantity.The reason is there is no value for Jan 1 2004 in database similary for Jan 31 2004 . So if you want the next date for which actual values are there put filter quantity is not null .
Still one more thing remains .You want to show only max and Min date quantities
use total( case when ( max_date =date) then (quantity) else (0) end).Use aggregate as calculated and roll up as total.Note this is very essential
as without putting total outside you will end up getting all weird answers
.
I dont know why i was thinking we cannot calculate Max and Min side by side.It took me a while to figure out.Below is the case
Use go sales ,sales namespace .Take Current year, date,Quantity ..filter current year to be 2004 .
1) try to take max(date for currentyear) and Min(date) for currentyear you will see that you are getting Jan 1 2004 and Jan 31 2004 .Which is as what you expect.
Now put quantity next to it.You will see that you get 0 for max quantity.The reason is there is no value for Jan 1 2004 in database similary for Jan 31 2004 . So if you want the next date for which actual values are there put filter quantity is not null .
Still one more thing remains .You want to show only max and Min date quantities
use total( case when ( max_date =date) then (quantity) else (0) end).Use aggregate as calculated and roll up as total.Note this is very essential
as without putting total outside you will end up getting all weird answers
.
Monday 9 January 2012
Cognos Sql Vs Native Sql and Performance
Hi
Native sql is the sql that is directly fired on the database. Cognos sql is the sql that is used by cognos once the output of the native sql have come.If your most of processing is done in the native sql.Your report will run faster.Simple Eg- If your filter for some reason such as data type casting are applied in the cognos sql.It means that all the data will be fetched first and then on it the filters will be applied.This will slow down the performance a lot.
You can notice that some time your report will have split queries in your native sql but the same queries are joined in the cognos sql .This means that cognos does know what is the path between those split tables .So the question is why does it split the queries and how to join them back.
Important :- If the queries are split then your performance is affected big time as the queries will be joined later by cognos.
The most simple reason for queries to split is the use of cognos function as simple as cast(abc,varchar(10)) ,since this is cognos cast and cannot be applied on the database it will be applied in congos sql , the query can split if you are applying a filter on this cast like this cast is used for date and you are saying date between report start date and report end date. Congos might consider the best way to do this is to split the queries apply the cast and filter based on cast on one query and then join both the queries .Now this is decided by congos logic and nothing you can do to control it .
Simple workaround : - Bring the column on which the cast is applied in the list or crosstab this might allow cognos to make a single native sql .
2) You can get rid of the cognos function by making use of similar database function,this will cause the processing to be transferred to database.
3) If you cannot find a way out and the project timeline is approaching you can make use of direct sql in queries .Take the two sql join together and fire on database .this is not the best way to do it due to maintainance concerns .But i have used this in one of my project.
Point to remember for good performance- Make sure your most of the filters come in the native sql and not in cognos sql. Try to use database function wherever possible so that most of cast , case ,count are handled by database this will speed up your performance.
Native sql is the sql that is directly fired on the database. Cognos sql is the sql that is used by cognos once the output of the native sql have come.If your most of processing is done in the native sql.Your report will run faster.Simple Eg- If your filter for some reason such as data type casting are applied in the cognos sql.It means that all the data will be fetched first and then on it the filters will be applied.This will slow down the performance a lot.
You can notice that some time your report will have split queries in your native sql but the same queries are joined in the cognos sql .This means that cognos does know what is the path between those split tables .So the question is why does it split the queries and how to join them back.
Important :- If the queries are split then your performance is affected big time as the queries will be joined later by cognos.
The most simple reason for queries to split is the use of cognos function as simple as cast(abc,varchar(10)) ,since this is cognos cast and cannot be applied on the database it will be applied in congos sql , the query can split if you are applying a filter on this cast like this cast is used for date and you are saying date between report start date and report end date. Congos might consider the best way to do this is to split the queries apply the cast and filter based on cast on one query and then join both the queries .Now this is decided by congos logic and nothing you can do to control it .
Simple workaround : - Bring the column on which the cast is applied in the list or crosstab this might allow cognos to make a single native sql .
2) You can get rid of the cognos function by making use of similar database function,this will cause the processing to be transferred to database.
3) If you cannot find a way out and the project timeline is approaching you can make use of direct sql in queries .Take the two sql join together and fire on database .this is not the best way to do it due to maintainance concerns .But i have used this in one of my project.
Point to remember for good performance- Make sure your most of the filters come in the native sql and not in cognos sql. Try to use database function wherever possible so that most of cast , case ,count are handled by database this will speed up your performance.
Subscribe to:
Posts (Atom)