Thursday, June 11, 2015

General Initialiation Block creation for Date Variables




As part of report development developers may requires to create variables for dates.below code is the generic one.So you can able to use it easily.Once its done then needs to create REP variables.
select
EXTRACT(yesr FROM sysdate) as Current_YR,
to_char(sysdate,'Q') as CURRENT_QR,
EXTRACT(month FROM sysdate) as CURR_MONTH_NUM,
trunc(trunc(sysdate,'MM')-1,'MM') AS FIRST_DAY_OF_LAST_MONTH,
trunc(sysdate,'MM')-1 AS LAST_DAY_OF_LAST_MONTH,
last_day(add_months(trunc(sysdate),-1))+1 AS FIRST_DAY_OF_CURR_MONTH,
last_day(trunc(sysdate)) AS LAST_DAY_CURR_MONTH,
NEXT_DAY (TRUNC(SYSDATE), 'SUNDAY') - 7 AS FIRST_DAY_OF_WK ,
NEXT_DAY (TRUNC(SYSDATE), 'SUNDAY') - 14 AS FIRST_DAY_OF_PREVIOUS_WK ,
NEXT_DAY (TRUNC(SYSDATE), 'SATURDAY') AS LAST_DAY_OF_WK,
NEXT_DAY (TRUNC(SYSDATE), 'SATURDAY')-7 AS LAST_DAY_OF_PREVIOUS_WK,
 trim(to_char(sysdate-1,'Month')) || ' '||to_char(sysdate-1, 'DD')||', '||to_char(sysdate-1, 'YYYY') as PREVIOUS_DAY_VARCHAR,
 (trunc(sysdate) + 2-7) - (TO_NUMBER(TO_CHAR(trunc(sysdate), 'D'), '99') ),
  (trunc(sysdate) + 1) - (TO_NUMBER(TO_CHAR(trunc(sysdate), 'D'), '99') ),
   ADD_MONTHS(TRUNC(SYSDATE,'MM'),-2)  AS FIRST_DAY_OF_TWO_MONTHS_AGO,
   ADD_MONTHS(TRUNC(SYSDATE,'MM'),-3)  AS FIRST_DAY_OF_THREE_MONTHS_AGO,
   ADD_MONTHS(TRUNC(SYSDATE,'MM'),-4)  AS FIRST_DAY_OF_FOUR_MONTHS_AGO,
(NEXT_DAY (TRUNC(SYSDATE), 'MONDAY') - 28) AS MONDAY_THREE_WKS_AGO,
(NEXT_DAY (TRUNC(SYSDATE), 'SUNDAY') - 21) AS SUNDAY_TWO_WKS_AGO,
to_char(sysdate,'MM')||to_char(sysdate,'YYYY') AS CURR_QUAL_MONTH,
to_char(add_months(sysdate,-1),'MMYYYY') AS PREVIOUS_QUAL_MONTH,
NEXT_DAY(TRUNC(SYSDATE), 'MONDAY') - 7 AS FIRST_DAY_OF_WK_MONDAY,
NEXT_DAY(TRUNC(SYSDATE), 'SUNDAY') AS LAST_DAY_OF_WK_SUNDAY,
trunc(trunc(sysdate,'YY')-1,'YY') AS FIRST_DAY_OF_LAST_YR
from dual;

Tuesday, June 3, 2014

General Errors

1)      General Errors




Above error occurs when Temp space is less.Same report will work without any errors if you apply any filters.So make sure to delete temp file data and try to increase the temp file size to overcome above issue.


2)  Errors like View Display Error
              
Exceeded configured maximum number of allowed output prompts, sections, rows, or columns.
  Error Details
Error Codes: IRVLJWTA:OI2DL65P
Location: saw.views.dashboard, saw.httpserver.processrequest, saw.rpc.server.responder, saw.rpc.server, saw.rpc.server.handleConnection, saw.rpc.server.dispatch, saw.threadpool.socketrpcserver, saw.threads
SQL Issued: 4308~80c8q8r5gug71seva984g62ahu

 For above error pls make sure to check your instance config file have below changes or not.If not please make it and restart all services.Report will disply without any error.

<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<!-- Oracle Business Intelligence Presentation Services Configuration File -->
<WebConfig xmlns="oracle.bi.presentation.services/config/v1.1">
   <ServerInstance>
      <!--This Configuration setting is managed by Oracle Enterprise Manager Fusion Middleware Control--><CatalogPath>C:\OBIEEHOME\instances\instance1\bifoundation\OracleBIPresentationServicesComponent\coreapplication_obips1\catalog\cpxbi</CatalogPath>
      <DSN>coreapplication_OH270360339</DSN>
      <Logging>
         <Writers>
            <!--This Configuration setting is managed by Oracle Enterprise Manager Fusion Middleware Control--><Writer implementation="FileLogWriter" name="Global File Logger" writerClassId="1" dir="{%ORACLE_BIPS_INSTANCE_LOGDIR%}" filePrefix="sawlog" maxFileSizeKb="10240" filesN="10" fmtName="ODL-Text"/>
            <!--This Configuration setting is managed by Oracle Enterprise Manager Fusion Middleware Control--><Writer implementation="CoutWriter" name="Console Logger" writerClassId="2" maxFileSizeKb="10240"/>
            <!--This Configuration setting is managed by Oracle Enterprise Manager Fusion Middleware Control--><Writer implementation="EventLogWriter" name="System Event Logger" writerClassId="3" maxFileSizeKb="10240"/>
            <!--  The following writer is not centrally controlled -->
            <Writer implementation="FileLogWriter" name="Webcat Upgrade Logger" disableCentralControl="true" writerClassId="5" dir="{%ORACLE_BIPS_INSTANCE_LOGDIR%}" filePrefix="webcatupgrade" maxFileSizeKb="2147483647" filesN="1" fmtName="ODL-Text"/>
         </Writers>
         <WriterClassGroups>
            <WriterClassGroup name="All">1,2,3,5,6</WriterClassGroup>
            <WriterClassGroup name="File">1</WriterClassGroup>
            <WriterClassGroup name="Console">2</WriterClassGroup>
            <WriterClassGroup name="EventLog">3</WriterClassGroup>
            <WriterClassGroup name="UpgradeLogFile">5</WriterClassGroup>
         </WriterClassGroups>
         <Filters>          
            <!--  These FilterRecords are updated by centrally controlled configuration -->
            <!--This Configuration setting is managed by Oracle Enterprise Manager Fusion Middleware Control--><FilterRecord writerClassGroup="File" path="saw" information="1" warning="31" error="31" trace="0" incident_error="1"/>
            <!--This Configuration setting is managed by Oracle Enterprise Manager Fusion Middleware Control--><FilterRecord writerClassGroup="File" path="saw.mktgsqlsubsystem.joblog" information="1" warning="31" error="31" trace="0" incident_error="1"/>
           
            <!--  The following FilterRecords are not centrally controlled -->
            <FilterRecord writerClassGroup="UpgradeLogFile" disableCentralControl="true" path="saw.subsystem.catalog.initialize.upgrade" information="1" warning="32" error="32" trace="1" incident_error="32"/>
            <FilterRecord writerClassGroup="UpgradeLogFile" disableCentralControl="true" path="saw.subsystem.catalog.upgrade" information="1" warning="32" error="32" trace="1" incident_error="32"/>           
         </Filters>
      </Logging>
      <!-- Placeholder Views node for later automated processing. -->
      <Views>
      <Pivot>
<!--This Configuration setting is managed by Oracle Enterprise Manager Fusion Middleware Control-->
<DefaultRowsDisplayedInDelivery>9999</DefaultRowsDisplayedInDelivery>
<!--This Configuration setting is managed by Oracle Enterprise Manager Fusion Middleware Control-->
<DefaultRowsDisplayedInDownload>9999</DefaultRowsDisplayedInDownload>
<!--This Configuration setting is managed by Oracle Enterprise Manager Fusion Middleware Control-->
<DisableAutoPreview>false</DisableAutoPreview>
<MaxVisibleColumns>3000</MaxVisibleColumns>
<MaxVisiblePages>10000</MaxVisiblePages>
<MaxVisibleRows>1000</MaxVisibleRows>
<MaxCells>40000</MaxCells>
<MaxVisibleSections>25000</MaxVisibleSections>
</Pivot>
<Cube>
<CubeMaxRecords>10000000</CubeMaxRecords>
<CubeMaxPopulatedCells>40000000</CubeMaxPopulatedCells>
</Cube>
<Table>
<!--This Configuration setting is managed by Oracle Enterprise Manager Fusion Middleware Control-->
<DefaultRowsDisplayedInDelivery>9999</DefaultRowsDisplayedInDelivery>
<!--This Configuration setting is managed by Oracle Enterprise Manager Fusion Middleware Control-->
<DefaultRowsDisplayedInDownload>9999</DefaultRowsDisplayedInDownload>
<MaxVisiblePages>1000</MaxVisiblePages>
<MaxVisibleRows>5000</MaxVisibleRows>
<MaxCells>40000</MaxCells>
<MaxVisibleSections>25000</MaxVisibleSections>
</Table>
</Views>
<Charts>
<SectionSliderDefault>5</SectionSliderDefault>
<SectionSliderLimit>100</SectionSliderLimit>
</Charts>  
   <Catalog>
         <UpgradeAndExit>false</UpgradeAndExit>
      </Catalog>
      <!--This Configuration setting is managed by Oracle Enterprise Manager Fusion Middleware Control--><Listener port="9710" ssl="false"/>
      <!--This Configuration setting is managed by Oracle Enterprise Manager Fusion Middleware Control--><JavaHostProxy ssl="false">
         <Hosts>
            <!--This Configuration setting is managed by Oracle Enterprise Manager Fusion Middleware Control--><Host name="coreapplication_obijh1" address="praveen.com" port="9810"/>
         </Hosts>
      </JavaHostProxy>
      <Alerts>
         <!--This Configuration setting is managed by Oracle Enterprise Manager Fusion Middleware Control--><ScheduleServer ssl="false" ccsPrimary="XXXXXX.com" ccsPrimaryPort="9706"/>
         <!--This Configuration setting is managed by Oracle Enterprise Manager Fusion Middleware Control--><OfflinePresentationServicesURL>http://IPADDRESS:7001/analytics/saw.dll</OfflinePresentationServicesURL>
      </Alerts>
      <ActionFramework>
         <!--This Configuration setting is managed by Oracle Enterprise Manager Fusion Middleware Control--><ActionExecutionServiceEndPoint>http://IPADDRESS:7001/analytics/actions/ActionExecutionService</ActionExecutionServiceEndPoint>
         <!--This Configuration setting is managed by Oracle Enterprise Manager Fusion Middleware Control--><TargetBrowsingServiceEndPoint>http://IPADDRESS:7001/analytics/actions/ActionRegistryService</TargetBrowsingServiceEndPoint>
         <!--This Configuration setting is managed by Oracle Enterprise Manager Fusion Middleware Control--><LocationServiceEndPoint>http://IPADDRESS:7001/analytics/actions/LocationService</LocationServiceEndPoint>
         <!--This Configuration setting is managed by Oracle Enterprise Manager Fusion Middleware Control--><WorkflowServer>http://IPADDRESS:7001</WorkflowServer>
         <WorkflowService>ANALYTICS</WorkflowService>
         <WorkflowSystem>obiaftests</WorkflowSystem>
      </ActionFramework>
   <Security>
<!--This Configuration setting is managed by Oracle Enterprise Manager Fusion Middleware Control-->
<ClientSessionExpireMinutes>210</ClientSessionExpireMinutes>
</Security>
<ODBC>
<!--This Configuration setting is managed by Oracle Enterprise Manager Fusion Middleware Control-->
<ResultRowLimit>9999999</ResultRowLimit>
</ODBC>
<!--This Configuration setting is managed by Oracle Enterprise Manager Fusion Middleware Control-->
<OracleHardwareAcceleration>false</OracleHardwareAcceleration>
<Dashboard>
<!--This Configuration setting is managed by Oracle Enterprise Manager Fusion Middleware Control-->
<ShowPageTabsAlways>false</ShowPageTabsAlways>
<!--This Configuration setting is managed by Oracle Enterprise Manager Fusion Middleware Control-->
<ShowSectionHeadingsDefault>false</ShowSectionHeadingsDefault>
<!--This Configuration setting is managed by Oracle Enterprise Manager Fusion Middleware Control-->
<CollapsibleSectionsDefault>true</CollapsibleSectionsDefault>
</Dashboard>
<BIComposer>
<!--This Configuration setting is managed by Oracle Enterprise Manager Fusion Middleware Control-->
<Enabled>true</Enabled>
</BIComposer>
<DeploymentProfile>
<!--This Configuration setting is managed by Oracle Enterprise Manager Fusion Middleware Control-->
<DeploymentMode>onsite</DeploymentMode>
</DeploymentProfile>
<AsyncLogon>
<!--This Configuration setting is managed by Oracle Enterprise Manager Fusion Middleware Control-->
<NewLogonWaitSeconds>30</NewLogonWaitSeconds>
<!--This Configuration setting is managed by Oracle Enterprise Manager Fusion Middleware Control-->
<NewSyncLogonWaitSeconds>900</NewSyncLogonWaitSeconds>
</AsyncLogon>
<Marketing>
<ContentServer>
<!--This Configuration setting is managed by Oracle Enterprise Manager Fusion Middleware Control-->
<URL>http://localhost:8765</URL>
</ContentServer>
</Marketing>
<Authentication>
<!--This Configuration setting is managed by Oracle Enterprise Manager Fusion Middleware Control-->
<EnabledSchemas>UidPwd,Impersonate,UidPwd-soap,Impersonate-soap</EnabledSchemas>
</Authentication>
</ServerInstance>
</WebConfig>


Wednesday, March 19, 2014

Browser Title change in OBIEE11g

Browser Title change process:
Before starting pls take the backup of below file.
Open the productmessages.xml from <Middleware_Home>\Oracle_BI1\bifoundation\web\msgdb\l_en\messages. Search for kmsgProductPortal attribute and change its value with your custom text.
Ex:

<WebMessage name=”kmsgProductPortal”>
<TEXT>Praveen BI Interactive Dashboards</TEXT>
</WebMessage>

save it and stop and start the Opmn to restart all OBIEE 11G services. Once the services are up, you will see the changed browser title as shown in below picture.

RPD Password RESET in OBIEE11g

As per my knowledge we cant able to reset the RPD password.But we can able to know already deployed RPD passwords with the help of Wlst script.

Like 10g in 11g also we can able to open RPD in Offline and Online mode.Better to memorize the password for future perspective.If we have process to reset pwd then developer can able to know the production rpd password.Then security issues come into pictuire.



Login to weblogic EM  and navigate to credentials store page as shown below.



Expand the key map oracle.bi.enterprise. It shows the earlier deployed RPD passwords.
In above image, I have 4 repositories and one scheduler schema keys in my credential key map. From the list, to get the password of “SampleAppLite” repository, follow these steps:
  • Open command prompt,
  • E:\>cd <OBIEE_HOME>\oracle_common\common\bin
  • Run wlst scrpt: <OBIEE_HOME>\oracle_common\common\bin>wlst.cmd
  • Connect to weblogic domain with the command:

    connect(“<weblogic_AdminUser>”,”<weblogic_AdminUser_Password>”,”<Adminserver_Hostname>:<Admin_Portno>”) 

    e.g: wls:/offline> connect(“weblogic”,”weblogic123″,”localhost:7001″)Connecting to t3://localhost:7001 with userid weblogic …Successfully connected to Admin Server ‘AdminServer’ that belongs to domain ‘bifoundation_domain’.————
  • Run listCred() command to get the password of SampleAppLite repository
    wls:/bifoundation_domain/serverConfig> listCred(“oracle.bi.enterprise”,”repository.SampleAppLite″)
    Location changed to domainRuntime tree. This is a read-only tree with DomainMBean as the root.
    For more help, use help(domainRuntime)

    [Name : tempvalue, Description : null, expiry Date : null]
    PASSWORD:Admin321
You will get the password: Admin321.

Tuesday, March 18, 2014

OBIEE 11g Presentation Server Catalog Replication Across Redundant Servers



Webcat Sync with other servers:The thing about OBIEE 11g, is that there is no single way to get things “up and running”. Over the past year I have performed countless OBIEE installs and almost every one has been different to the last, due to infrastructure challenges, scale requirements or requirements for high-availability and server redundancy.
There is little we can do about infrastructure challenges, typically around policies which are in place across the IT estate and must be adhered to. Scale is simple; more users means more or bigger servers. The last point on the list is HA & server redundancy, and this is arguably the biggest area we need to address before punching in “./runInstaller” at the command-prompt.
Mark Rittman is currently pulling a wider blog posts around options for HA & server redundancy, so I’m not about to get into that in this post. What I will share in my first Rittman Mead blog post is something I have been working on recently, that is sometimes implemented as part of a HA/redundancy solution; BI Presentation Server catalog replication.

BI Presentation Server catalog replication is the Oracle supported method of moving a BI Presentation Server catalog from one running OBIEE instance to another. This is useful is many situations namly:
  • Release cycle management when specific aspects of the BI Presentation Server catalog need to be moved from one OBIEE environment to another, such as development to testing.
  • Full BI Presentation Server catalog synchronization between OBIEE instances to provide two OBIEE instances with exactly the same BI Presentation Server catalog for DR.
The 2nd point above is the scenario I am looking to cover here – using this utility we can make sure the BI Presentation Server catalog on two distinct OBIEE instances are the same. In a DR event we can either point users at the secondary server or reverse this utility to pump the data from the DR server to the primary server after the fault on the server has been cleared.  The RPD and other changes, such as user roles, would be managed as part of the release process and copied to the DR server when they are pushed to the production server. It is only the BI Presentation Server catalog which is constantly changing as the OBIEE instance is used, and therefore only the BI Presentation Server catalog which needs to be constantly replicated.
To set up BI Presentation Server catalog replication, we can use a command-line utility which ships with Oracle Business Intelligence 11g, called either “sawrepaj.bat” or “sawrepaj.sh” depending on your operating system (the two are functionally identical, and differ only in the way they are invoked from the command-line). “sawrepaj” has options to synchronise catalogs between servers, or to simply export a catalog for backups or as part of the release lifecycle; in addition, we can control the grain of what is exported down to an individual catalog folder or file.
The utility runs from the command-line and accepts a number of options, or commands, to specify how the replication takes place, using the following syntax:
sawrepaj.sh [/C path] command [command parameters]
  • mark – instruct the OBIEE server to ‘watch’ catalog files and record changes.
  • run – run the batch, as defined in the config.xml file.
Before using the utility, we need to create a config.xml file which instructs the OBIEE Presentation Services to record changes, and tells sawrepaj what to do when it executes.
The config.xml file can be placed anywhere in the filesystem and is referenced using the /C switch when calling sawrepaj – more about this /C switch later. The config file tells the sawrepaj utility which Oracle BI Presentation Servers are involved in the replication, and how to access them. The example below includes a source and target Oracle BI Presentation Server – however you could add countless other BI Presentation Servers depending on your environment.
<?xml version="1.0" encoding="UTF-8"?>
<!-- Oracle BI Presentation Services Replication Agent Configuration File -->
<Config>
<General>
<LogExpiresHours>48</LogExpiresHours>
</General>
<Server name=”serverA" user="weblogic" pwd="Password01">
<Property name= "oracle.bi.presentation.url"
<Property name="oracle.bi.presentation.usesoaptcptransport" value="N"/>
</Server>
<Server name="serverB" user="weblogic" pwd="Password01">
<Property name= "oracle.bi.presentation.url"
<Property name="oracle.bi.presentation.usesoaptcptransport" value="N"/>
</Server>
<Folderset name="all">
<Folder>/</Folder>
</Folderset>
<ReplicationTask destination="serverA" source="serverB" folders="all" />
</Config>
The three sections of interest in the above file are Server, FolderSet and ReplicationTask. Each
  • Server tag points to an OBIEE Presentation Services instance with credentials for a user with has SOAP access.
  • FolderSet tag contains a list of Folder tags which detail the folders within the webcat to replicate.
  • ReplicationTask contains the instruction of what to do when we run sawrepaj. For the above example, serverA is the target and ServerB is the source.
Before any replication can begin, we need to enable each BI Presentation Server component for replication. Add the below to the instanceconfig.xml file between the <catalog> tags. This needs to be done for all all Presentation Server components, whether source or target for the replication.
<Replication>
<Enabled>true</Enabled>
<ReadLogRecordsSinceHoursAgo>120</ReadLogRecordsSinceHoursAgo>
<RecordsInFileLimit>4000</RecordsInFileLimit>
</Replication>
Now, back to the sawrepaj commands:
mark – The mark command is used to tell tBI Presentation services which webcat files to monitor for changes. We can be as specific as a single file, or as as vague as an entire webcat. You will need to run this on a BI server with the following command:
sawrepaj mark all /
run – The final part of the process, the run command will instruct sawrepaj to run all the tasks in the config.xml file. The below is an example of the command. If your config.xml file is not in the root path of the sawrepaj tool, you will need to specify it’s location with the /C switch. This command can be added to a cronjob, a Windows scheduler or can be ran manually, or as part of another script.
sawrepaj /C /my/config/file/config.xml run
That’s it! Once completed, the above steps will give you all you need to set up full catalog replication between multiple BI servers

Tuesday, April 2, 2013

OBIEE 11g Architecture


Web Logic Server Domain :
J2EE App Server used across the board for all 11g BI applications Contains :
Managed Server : Set of J2EE Applications used for “functioning” the BIEE system
Admin Server : Set of J2EE Applications used for “administering” of BI EE system
Oracle Process Manager and Notification Server (OPMN) domain :
Used to start/Stop system components (BI Svr, BI Pres Svr, BI Schdlr, BI ClustrCntrl)
Can be accessed from CMD or from EM page(GUI)
 Admin Server Components
WLS Admin Console
Admin GUI for WLS,Security and J2EE Components
Fusion Middleware-EM Control (FMW EM)
Admin GUI to Manage the BI Domain
JMX Beans
Java components that provide programmatic access for managing a BI domain.
Managed Server Components:
BI Plugin : Sends web http requests to BI Presentation Services
BI Security :Integrates BI Server and FMW sec platform(using webservice calls)
BI Action Services: Dedicated web services for Action framework
BI Web Service SOA: Provides Web services for objects in the BIEE Presentation Catalog, to invoke analysis, agents, and conditions.
BI Office: Provides the integration between Oracle Business Intelligence and Microsoft Office products
BIEE Domain System Components:
BI Server :
Provides capabilities to query and access data as well as services for accessing and managing the RPD file (BIEE Metadata).
BI Presentation Services:
Provides the framework and interface for the presentation of business intelligence data to Web clients. It maintains an Oracle BI Presentation Catalog service on the file system for the customization of this presentation framework.
BI Scheduler :    
 Provides framework for scheduling and delivering reports to users (used by delivers)
BI Javahost :
Enables BI Presentation Services to support various components: Java tasks for BI Scheduler, BI Publisher, and Graph generation.
BI Cluster Controller:
Used for distributing requests to BI server and ensure load balancing
Files:
Repository file (e.g. SampleSales.rpd)
Config Files (nQconfig.ini,instanceconfig.xml,)
Log Files (nqserver.log,nqquery.log, nqscheduler.log, sawlog0.log etc)
Presentation catalog (<MW_HOME>\OracleBIPresentationServicesComponent\coreapplication_obips1\catalog)

Tuesday, January 22, 2013

OBIEE11g RPD Deployment


Login to Oracle Enterprise Manager [Fusion Middleware Console]