Wednesday, September 5, 2012

Some cognos tutorial

Thursday, March 29, 2012

Query builder query



Universe vs report.
select si_id, si_name from ci_infoobjects, ci_appobjects where PARENTS("SI_NAME='Webi-Universe'", "SI_NAME='<universe name>'") and si_kind = 'Webi'
-----------------------------------------------------------
To get all universe data including a WebI report list
SELECT *
FROM CI_APPOBJECTS
WHERE SI_KIND='Universe' AND SI_NAME='CCE Mass Channel'


Then, use the list of IDs to get a list of report names that use this Universe
SELECT SI_NAME,SI_DESCRIPTION, SI_ID,SI_AUTHOR,SI_PARENT_FOLDER,SI_UNIVERSE,SI_HAS_PROMPTS
FROM CI_INFOOBJECTS
WHERE SI_ID IN (nnnnn,nnnnn,nnnnn)
-------------------------------------------------------
How do I get a list of all connections matching a substring
SELECT SI_NAME
FROM CI_APPOBJECTS
WHERE SI_KIND='MetaData.DataConnection' AND SI_NAME LIKE '%CFD%'
-------------------------------------------------------
To list all WebI reports when objects exceed 1000
SELECT SI_TARGETID,SI_KIND,SI_UPDATE_TS,SI_NAME,SI_ID,SI_SENDABLE,SI_OWNER,SI_FILES,SI_DESCRIPTION,SI_UNIVERSE,SI_PARENT_FOLDER,SI_OWNERID,
SI_SUBMITTERID,SI_STARTTIME,SI_ENDTIME,SI_WEBI_PROMPTS,
FROM CI_INFOOBJECTS
WHERE SI_KIND='Webi' and SI_NAME > 'L'


then use <= 'L'
-----------------------------------------------------------
To list all report folders containing a string
SELECT *
FROM CI_INFOOBJECTS
WHERE SI_NAME LIKE '%Dealer%' AND SI_KIND='Folder'


For Universe folders, use CI_APPOBJECTS
------------------------------------------------------------
To list all Webi reports containing a string
SELECT *
FROM CI_INFOOBJECTS
WHERE SI_NAME LIKE '%Dealer%' AND SI_KIND='Webi'
--------------------------------------------------------------------------------------------------
SELECT SI_NAME, SI_SHORTNAME, SI_DESCRIPTION, SI_ID, SI_PARENT_FOLDER, SI_OWNER, SI_CREATION_TIME
FROM CI_APPOBJECTS
WHERE SI_KIND='Universe'

Wednesday, November 30, 2011

A note on CUID



What are CUID? 


CUID- Cluster Unique Identifier 


The CUID is a key to identify the Universe or report within the same cluster, may be in different deployment environment(e.g. development, staging and production). It is a 24 bit key which automatically generated when first export to repository is done. It is the single identification of the reports or the Universe which do the all handshake for the movement of the data through all BO servers ( Job server, Cache server, processing server).


Now what is cluster in sense of BO? 


According to the Admin guide A cluster consists of two or more CMS servers working together against a common CMS system database. If a machine that is running one CMS fails, a machine with another CMS will continue to service BusinessObjects Enterprise requests. This "high availability" support helps to ensure that BusinessObjects Enterprise users can still access information when there is an equipment failure.
As an Example there are three environment in a BO implementation Development , Staging and Production. They have their own CMS server. But to work effectively it bundles into same cluster and controlled by one of the three CMS among them or Create another CMS to to control the cluster.


Where we can find this? 
You can find this by opening the Universe or Report properties in CMC. 





What is actually affected if CUID is changed?


All the reports are connected to the Universe by the CUID. If the CUID got changed all the reports will throw “Universe Not Found” error. 
We need to re-point the report to the new universe. 


In What situation it got changed? 


I have not all the scenario but I found the below scenario: 




  • Import a universe. 
  • Save it in different file name. 
  • Export it in same path
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~


  • Import a universe. 
  • Save it in same file name. 
  • Change the Universe name from properties
  • Export it in same path
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~


  • Import and save a universe. 
  • Export it in different path
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~


  • Import and save a universe. 
  • Change the connection 
  • Export it in same path
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~


  • Import and save a universe. 
  • Change the connection 
  • Export it in same path





If mistakenly it got changed then what is the recovery method without changing all the report?


1. Open the backup copy of the universe 
2. Name it the same as the original (the one all the reports point to) 
3. Export the universe to the same folder the original resides in 
4. You will be asked if you want to copy/move the universe to the new location, select Copy 
5. You will be told that a universe with that name already exists, do you want to overwrite it, select Yes 

Some other points


1. CUID never gets changed when importing from one CMS to other using LCM or Import Wizard. 
2. CUID is unique but ID which comes along with may be multiple for a single universe. ID is not used except auditing.

View or View on Demand




When to give access level View or View on Demand?




View access level


User’s benefit

  1. User can see the html report page which was saved when last refreshed.
  2. They don’t have the refresh report option enable.
  3. They can do all the report operation without touching the database.

System’s benefit

  1. When user refreshes the report the generated SQL query directly goes to database and ran it in database server. If they get the view access only they the system overload will be less as there will not be simultaneous run (same report in same point-of-time with multiple login) for the same query for different users.
  2. It reduces number of hit to database server.
  3. It reduces the network traffic.
  4. Report instance which a user is using having full capability ( like drill down , snapshot… ) but those activity will not send any SQL to Database server. It will go to the report server to get the copy of data saved in last refresh.
  5. There will be no chance of atomicity problem if we scheduled the report refresh after the regular database refresh.

Developer’s burden
Developers need to do the following:

  1. Either they need to schedule all the reports with proper prompt value so the reports can refresh automatically according to the prompt and return the proper data.
  2. Or they have to manually refresh the reports before the users’ starts using everyday. It may be daily, weekly, monthly, and yearly, quarterly or user assigned.





View On Demand access level
User’s benefit

  1. Users will get access to the real-time data.
  2. They can refresh their own report by changing the condition.
  3. Users will get the accurate data till the second of their refreshing time.
  4. Response time may be decreased but the accuracy of data is more perfect.
  5. User can change the condition of the report.
  6. If the reports are scheduled then the user have both choice to see the refreshed report or refresh it again to see the last minute update.

System’s benefit or Burden:

  1. Number of database server hit will be more.
  2. In time of yearly or quaterly closing the network traffic will be most.
  3. The same query will run thousand times for different users.

Developer’s burden or benefit:

  1. Need to tune the database and do the performance testing continuous.
  2. Do a perfect load balancing.
  3. No need to scheduled the reports or no need to refreshing and saving the reports. 


Tuesday, November 22, 2011

Default Time stamp problem.





Problem: 
User required an object called “Today” which contains todays date. But they don’t want the default time stamp added as a suffix of that. They want onlt “11/22/2011” in place of “11/22/2011 11:07:17 AM”. BO Does not provide a direct option for removing the attached timestamp it can be achieved using following step. 

Solution: 

1. Create an date object name “Today” and select “sysdate” for this object. 
2. Create another character object “Today 1” and select “to_char(trunc(@Select(<Class Name>\Today)),'mm/dd/yyyy')” 
3. Copy the List Name from the Properties tab. 
4. Open the “Today” objects property tab and change the List name as the List name of “Today 1”’ List name.
5. Now see the Display value of “Today” 

6. Now delete the “Today 1” object. 
7. Export the Universe.


Partial result





Problem:
While Refreshing a report in Business Objects returned a warning “Partial Results”. Also, some of the data which was required in the report was missing or not being fetched.
Analysis:
Extract the same SQL generated by the report and run it in the same database on which the Universe is built. Count the rows of data.

1) Open the universe
2) Click on File, then Parameters
3) Click on the Controls Tab
4) Check the “Limit size of the result to” text box.

If the number of rows set less than the number of rows returns the SQL query then it throws partial and random records.

Solution:
1.      Change the number of rows in the Designer more than the query returned
2.      Uncheck the checkbox of “Limit size of the result to”

Risk & Best Practices:

1.      Universe is normally built for many reports so before changing the number of rows it is better to discuss with Business that what extent they want the data and change upto that number of rows. In future if another report is giving the same partial result error fix the report not the Universe as the report is extreacting more than the data needed.
2.      The limit should be a realistic number 100,000 record is good for any reporting environment. More than that will effect system performance.
3.      If you uncheck the checkbox: The limit should be set to the maximum data retrieved by Oracle then there is a risk that Cartesian products would be allowed to run and return millions of rows.

Monday, November 21, 2011

BI 4.0 some good tutorial


MultiSource Universe (Part1) - SAP Business Objects 4.0 - Information Design Tool
http://www.youtube.com/watch?v=4zeB9A0gJEg

MultiSource Universe (Part 2) - SAP Business Objects 4.0 Information Design Tool
http://www.youtube.com/watch?v=jtiyBTgnXmQ

How to create a universe (In Local Folder) - SAP Business Objects XI 4.0 - Information Design Tool
http://www.youtube.com/watch?v=pgzzgMJfaLs

Row Level Security in SAP Business Objects by Creating a Data Security Profile
http://www.youtube.com/watch?v=6yM0OLK6QlI

Change Data Source Web Intelligence 4.0
http://www.youtube.com/watch?v=BdjiLu977Hg

SAP Business Objects Explorer
http://www.youtube.com/watch?v=qpQ2m2rwU40

Business Objects Universe
http://www.youtube.com/watch?v=p6iBntoYGOQ

Web Intelligence 4.0 Tutorial
http://www.youtube.com/watch?v=sD_MFrSOZAs

Cascading List of Values (associating to Parameters in the Business Layer)
http://www.youtube.com/watch?v=_1Qyb8X3ZeQ

Cascading LOV (associating to Dimension Objects in the Business Layer)
http://www.youtube.com/watch?v=7i4T0NgGVAg

LOV in Business Objects (based on Custom Hierarchy)
http://www.youtube.com/watch?v=8jYuMG5IG8U

SAP HANA Modeling
http://www.youtube.com/watch?v=9hM83qw9tYs

Score Cards on iPad - SAP Business Objects Mobile
http://www.youtube.com/watch?v=rfqawiAwqk4

Parameters and List of Values in SAP Business Objects 4.0
http://www.youtube.com/watch?v=FuDD-48pSCc

MultiSource Universe (Part1) - SAP Business Objects 4.0 - Information Design Tool
http://www.youtube.com/watch?v=4zeB9A0gJEg

MultiSource Universe (Part 2) - SAP Business Objects 4.0 Information Design Tool
http://www.youtube.com/watch?v=jtiyBTgnXmQ