Use could usefunction Set.Item(0)
Example:
SELECT {{[Date].[Calendar].[Calendar Year].Members
}.Item(0)}
ON 0
FROM [Adventure Works]
Tuesday, May 10, 2011
How can we setup default dimension member in Calculation script?
We can use ALTER CUBE statement.
Syntax:
ALTER CUBE CurrentCube | YourCubeName UPDATE DIMENSION , DEFAULT_MEMBER='';
Syntax:
ALTER CUBE CurrentCube | YourCubeName UPDATE DIMENSION , DEFAULT_MEMBER='';
How to start & stop IOP Server
D:\Oracle\Middleware\user_projects\epmsystem1\bin\startIOPServer_iopinstance1.bat.
D:\Oracle\Middleware\user_projects\epmsystem1\bin\stopIOPServer_iopinstance1.bat.
where D:\Oracle\Middleware is the installation directory for EPM System
D:\Oracle\Middleware\user_projects\epmsystem1\bin\stopIOPServer_iopinstance1.bat.
where D:\Oracle\Middleware is the installation directory for EPM System
How to bring the model back to an unpublished state:
1.Export the model to a file system:
“exportmodel –u admin –p”
2 Reset and restart the system:
a. Stop the server.
b. Run isreset.
c. Run startserver.
d. In a new window, run “initializesystem –u admin –p”
3 Go to/custom/bin and import the exported model:
“isadmin –u admin –p-f “importmodel_export.isa””
You can then go back to the Administration Workbench, make any desired changes, and publish the model again.
“exportmodel –u admin –p
2 Reset and restart the system:
a. Stop the server.
b. Run isreset.
c. Run startserver.
d. In a new window, run “initializesystem –u admin –p
3 Go to
“isadmin –u admin –p
You can then go back to the Administration Workbench, make any desired changes, and publish the model again.
How to create a script to import and publish a worksheet, a workbook, and an analysis type in IOP
Scripts are created by an administrator on the Administration / Script Templates page in the Administration Workbench.
We can create a script to import and publish a worksheet, a workbook, and an
analysis type.
alter model schema set current edit
import worksheet definition from file "xxxxxxxx.xml”
import workbook definition from file "xxxxxxxx.xml"
import analysistype definition from file "xxxxxxxxxxxx.xml"
publish metadata changes.
To execute a script:
1 In the Planning Workbench navigation panel, select Scripts.
2 Select a script and click Execute.
We can create a script to import and publish a worksheet, a workbook, and an
analysis type.
alter model schema set current edit
import worksheet definition from file "xxxxxxxx.xml”
import workbook definition from file "xxxxxxxx.xml"
import analysistype definition from file "xxxxxxxxxxxx.xml"
publish metadata changes.
To execute a script:
1 In the Planning Workbench navigation panel, select Scripts.
2 Select a script and click Execute.
Monday, May 9, 2011
Scripts Templates
1.How to Set the model schema to edit mode.
Alter model schema set current edit.
2.How to Set the schema to shadow in backend and clear any previous information.
set schema shadow
clear schema shadow.
3.how to generate planning worksheet from modified XML files.
alter model schema set current edit
import worksheet definition from file "${interlace.home}\custom\workbook\worksheet_Standard Cost Delta.xml"
publish metadata changes
...more
Alter model schema set current edit.
2.How to Set the schema to shadow in backend and clear any previous information.
set schema shadow
clear schema shadow.
3.how to generate planning worksheet from modified XML files.
alter model schema set current edit
import worksheet definition from file "${interlace.home}\custom\workbook\worksheet_Standard Cost Delta.xml"
publish metadata changes
...more
How do you calculate monthly average of a year, optionally including empty months?
Lets say we would like to calculate average of Internet Order count from Adventure works database for year 2004. Lets first check what data we have in database:
SELECT {[Measures].[Internet Order Count]} ON 0
, Descendants([Date].[Calendar].[Calendar Year].&[2004], [Date].[Calendar].[Month]) ON 1
FROM [Adventure Works]
WHERE ([Product].[Product Model Lines].[Model Name].&[Classic Vest])
We included filter for Product so numbers would be easier to understand.
Results of above query:
Internet Order Count
January 2004 51
February 2004 52
March 2004 53
April 2004 60
May 2004 57
June 2004 49
July 2004 35
August 2004 (null)
Total sum of these values is 357.
As we counted for 8 months, average value would be 357/8= 44.625
If we exclude August with value NULL, average value would be 357/7=51
To get Average value we can use following query:
WITH MEMBER [Measures].[AvgVal] AS
Avg( Descendants([Date].[Calendar].[Calendar Year].&[2004]
, [Date].[Calendar].[Month])
, [Measures].[Internet Order Count]
)
SELECT {[Measures].[AvgVal]} ON 0
FROM [Adventure Works]
WHERE ([Product].[Product Model Lines].[Model Name].&[Classic Vest])
Result of this query:
AvgVal
51
As you see MDX function AVG did not include August month into calculation as for that month measure value is NULL.
If we want to include NULL value, we have to replace value NULL for our measure with value 0. This could be done using CoalesceEmpty function. So our query will be:
WITH MEMBER [Measures].[AvgVal] AS
Avg( Descendants([Date].[Calendar].[Calendar Year].&[2004]
, [Date].[Calendar].[Month])
, CoalesceEmpty([Measures].[Internet Order Count], 0)
)
SELECT {[Measures].[AvgVal]} ON 0
FROM [Adventure Works]
WHERE ([Product].[Product Model Lines].[Model Name].&[Classic Vest])
And now results will be:
AvgVal
44.625
SELECT {[Measures].[Internet Order Count]} ON 0
, Descendants([Date].[Calendar].[Calendar Year].&[2004], [Date].[Calendar].[Month]) ON 1
FROM [Adventure Works]
WHERE ([Product].[Product Model Lines].[Model Name].&[Classic Vest])
We included filter for Product so numbers would be easier to understand.
Results of above query:
Internet Order Count
January 2004 51
February 2004 52
March 2004 53
April 2004 60
May 2004 57
June 2004 49
July 2004 35
August 2004 (null)
Total sum of these values is 357.
As we counted for 8 months, average value would be 357/8= 44.625
If we exclude August with value NULL, average value would be 357/7=51
To get Average value we can use following query:
WITH MEMBER [Measures].[AvgVal] AS
Avg( Descendants([Date].[Calendar].[Calendar Year].&[2004]
, [Date].[Calendar].[Month])
, [Measures].[Internet Order Count]
)
SELECT {[Measures].[AvgVal]} ON 0
FROM [Adventure Works]
WHERE ([Product].[Product Model Lines].[Model Name].&[Classic Vest])
Result of this query:
AvgVal
51
As you see MDX function AVG did not include August month into calculation as for that month measure value is NULL.
If we want to include NULL value, we have to replace value NULL for our measure with value 0. This could be done using CoalesceEmpty function. So our query will be:
WITH MEMBER [Measures].[AvgVal] AS
Avg( Descendants([Date].[Calendar].[Calendar Year].&[2004]
, [Date].[Calendar].[Month])
, CoalesceEmpty([Measures].[Internet Order Count], 0)
)
SELECT {[Measures].[AvgVal]} ON 0
FROM [Adventure Works]
WHERE ([Product].[Product Model Lines].[Model Name].&[Classic Vest])
And now results will be:
AvgVal
44.625
Not getting data in row source table
create a BOM_DS and BOM_RS is created automatically (rowsorce option checked while creating DS) and following a tables are created at back end
1)RS_SHPMNT_RS_STG
2)RS_SHPMNT_RS
3)RS_SHPMNT_RS_DIF.
In order to get data in RS_table we have to add in xml file:.
< stage-rowsource rowsource="BOM_RS">
1)RS_SHPMNT_RS_STG
2)RS_SHPMNT_RS
3)RS_SHPMNT_RS_DIF.
In order to get data in RS_table we have to add in xml file:.
< stage-rowsource rowsource="BOM_RS">
Intial IOP setup.
As part of your initial setup, you need to do 3 main steps:
- isreset
- startserver
- initializesystem.
- isreset
- startserver
- initializesystem.
how to reset the IOP admin password
When you install Integrated Operational Planning, the default administrator password is
“password.”
To change the default password:
1. Stop the server.
2. Add the following line to the.properties file:
admin.password=
3. Enter the following command to put the password in encrypted form:
encrypt
4. Copy and paste the output as follows:
# encrypt and copy the output (encrypted password) here
admin.password={3DES}F7zVJml7BfKDaM2S67s4Hw==
5. Restart the server.
“password.”
To change the default password:
1. Stop the server.
2. Add the following line to the
admin.password=
3. Enter the following command to put the password in encrypted form:
encrypt
4. Copy and paste the output as follows:
# encrypt
admin.password={3DES}F7zVJml7BfKDaM2S67s4Hw==
5. Restart the server.
What is the role of row source in IOP
Rowsource is an IOP internal table that can be used for planning. You can do what-if analysis on teh rowsource data, and it will propagate the changes to the cubes that the rowsource is linked to.
Rowsource is of two types: regular (it will copy the data over and expose for what-if analysis) and system (it is a pass through, the data is directly loaded from the data source). You cannot do direct what-if on that data.
Rowsource is of two types: regular (it will copy the data over and expose for what-if analysis) and system (it is a pass through, the data is directly loaded from the data source). You cannot do direct what-if on that data.
Saturday, May 7, 2011
can we pass wildcard(%) parameters to MDX and RSQL
Instr Function.
go through this:-
http://publib.boulder.ibm.com/infocenter/dzichelp/v2r2/index.jsp?topic=/com.ibm.dwe.cubemdx.doc/mdx_instr.html
go through this:-
http://publib.boulder.ibm.com/infocenter/dzichelp/v2r2/index.jsp?topic=/com.ibm.dwe.cubemdx.doc/mdx_instr.html
Is it possible to to pass parameters to MDX queries -
Yes.
Eg:-select {[Fiscal_ Dimension].[FY 2011]} on columns from [Cube_name] where (Parameter(“Product_Name”, MEMBER,[PRODUCT_ Dimension]))
When the above query run, Query pause during preview whenever a Parameter(...) statement is encountered in the query definition. A dialog box opens, prompting you to enter a value for the parameter. Enter a value and click OK. According the value it is filtering the value at runtime.
Eg:-select {[Fiscal_ Dimension].[FY 2011]} on columns from [Cube_name] where (Parameter(“Product_Name”, MEMBER,[PRODUCT_ Dimension]))
When the above query run, Query pause during preview whenever a Parameter(...) statement is encountered in the query definition. A dialog box opens, prompting you to enter a value for the parameter. Enter a value and click OK. According the value it is filtering the value at runtime.
Is it possible to pass parameters to RSQL Queries defined in IOP?
Yes,we can pass the parameter to RSQL queries
Eg: select * from Product where product=?
Eg: select * from Product where product=?
Steps to migrate a Model
Migration Process:
On Source System:-
1.Make sure all the IOP services are up and running.
2.preparemigration.bat (C:\oracle_iop\bin.
3.Copy EXPORT and CUSTOM folders from INSTALL_ROOT (C:\oracle_iop) to the target IOP Server.
On Target System:-
1.Backup site.properties file (C:\oracle_iop\custom\config)
2.Stop the IOP service
3.Run isreset.bat (C:\oracle_iop\bin)
4.Copy EXPORT and CUSTOM folders to C:\oracle_iop\
5.Restore the site.properties file that is backed-up earlier
6.Start the IOP service
7.Manually Create all the users existing in source system
8.Run initializesystem.bat
C:\oracle_iop\bin>initializesystem.bat -u Admin -p welcome1234
9.Run bootstrap.bat
C:\oracle_iop\custom\bin>bootstrap.bat -u Admin -p welcome1234
10.Check for errors or warnings
On Source System:-
1.Make sure all the IOP services are up and running.
2.preparemigration.bat (C:\oracle_iop\bin.
3.Copy EXPORT and CUSTOM folders from INSTALL_ROOT (C:\oracle_iop) to the target IOP Server.
On Target System:-
1.Backup site.properties file (C:\oracle_iop\custom\config)
2.Stop the IOP service
3.Run isreset.bat (C:\oracle_iop\bin)
4.Copy EXPORT and CUSTOM folders to C:\oracle_iop\
5.Restore the site.properties file that is backed-up earlier
6.Start the IOP service
7.Manually Create all the users existing in source system
8.Run initializesystem.bat
C:\oracle_iop\bin>initializesystem.bat -u Admin -p welcome1234
9.Run bootstrap.bat
C:\oracle_iop\custom\bin>bootstrap.bat -u Admin -p welcome1234
10.Check for errors or warnings
Oracle IOP
Oracle Integrated Operational Planning (IOP) integrates financial and operational planning with business intelligence to drive timely, fact-based decision-making across manufacturing, sales, and marketing functions.
Subscribe to:
Posts (Atom)