Oracle Olap开发备忘录

以前曾开发了基于Oracle Olap服务的应用，趁着没有完全忘记赶紧记录下来。
1、很早以前就针对Oracle 8里面的Express Server作了开发，当时是使用dml的C接口，在Java

里面通过JNI访问，虽然只有一个命令接口，但是着实费力，同时JNI对J2EE Application

Server也有所影响。
2、这一次支持的是Oracle 9i与10g 的Olap Server，虽然Oracle提供了Java Olap API但是

还存在一些版本差别，应该是release 4以上才比较一致把。9i低版本需要注意

oracle.express.mdm.MdmMetadataProvider与

oracle.olapi.metadata.mdm.MdmMetadataProvider之间的转换，可使用

oracle.express.mdm.MdmMetadataProvider.getNewMdmMetadataProvider()方法将

Express的Provider对象转换为Olapi的对象.
3、Oracle BI Beans为开发提供了一定的便利，但是由于我们需要比较原始的逻辑，BI Beans不

适合。
4、Oracle Olap API虽与JOlap不兼容，但是两者的概念非常类似，他们与其他API最大的不同之

处是非常强调元数据与数据的区别，这一点从两者的文档中可以直观的看到：直接的对象体系及操作是

没有办法拿到任何数据的，包括维度、成员、层次、属性等；必须首先获得描述这些数据的元数据的

Source属性对象，才能够通过Cursor访问到数据。这一点很欣赏。仔细学习可以领会很多olap元数据的实现思路。

5、开发时直接使用Rolap测试数据就行了，analysis workspace是使用物化视图模拟Molap，两者在开发编程方面完全一致，而后者还额外需要一些处理步骤才能够被Java Olap API访问到，不必麻烦。

测试用例：
//连接，并列出维及成员
package Mr.JBean.test;

import java.util.*;
import junit.framework.TestCase;

import oracle.olapi.metadata.mdm.*;
import oracle.olapi.data.source.*;
import oracle.olapi.data.cursor.*;
import globalExamples.*;
import oracle.express.olapi.data.full.ExpressDataProvider;
import oracle.express.olapi.transaction.ExpressTransactionProvider;

public class TestOracleOlap extends TestCase {

    ExpressTransactionProvider tp = null;
    MdmMetadataProvider mp = null;
    MdmSchema rootSchema = null;
    List dims = null;
    ExpressDataProvider dp = null;
    CursorPrintWriter cpw = null;

    public TestOracleOlap(String name) {
        super(name);
    }

    protected void setUp() throws Exception {
        super.setUp();
        cpw = new CursorPrintWriter();

        try {
            Class.forName("oracle.jdbc.driver.OracleDriver");
        } catch (ClassNotFoundException e) {
            System.out.println("Could not load the JDBC driver. " + e);
        }

        String url = "jdbc:oracle:thin:@host:port:instance";
        String user = "sh";
        String password = "sh";

        oracle.jdbc.OracleConnection conn = null;
        try {
            conn = (oracle.jdbc.OracleConnection)
                   java.sql.DriverManager.getConnection(url, user,

password);
        } catch (Exception e) {
            System.out.println("Connection attempt failed. " + e);
        }

        tp = new ExpressTransactionProvider();

        dp = new ExpressDataProvider(conn, tp);
        try {
            dp.initialize();
        } catch (Exception e) {
            e.printStackTrace();
        }

//      oracle.express.mdm.MdmMetadataProvider emp = null;  //对于9i低版本
        try {
//            emp = (oracle.express.mdm.MdmMetadataProvider) dp.
//                  getDefaultMetadataProvider();
//            mp = emp.getNewMdmMetadataProvider();
            mp = (MdmMetadataProvider) dp.getDefaultMetadataProvider();
            if (mp == null)
                System.out.println("Cannot create the MDM metadata

provider. ");
        } catch (Exception e) {
            e.printStackTrace();
        }

    }

    public void testOlap() throws Exception {
        if (mp == null) {
            System.out.println("Cannot create the MDM metadata provider.

");
            return;
        }
        rootSchema = mp.getRootSchema();
        List subSchemas = rootSchema.getSubSchemas();
        Iterator schIter = subSchemas.iterator();
        while (schIter.hasNext()) {
            MdmSchema subSch = (MdmSchema) schIter.next();
            System.out.println(
                    "/n/n============================/nSubSchema Name: "

+
                    subSch.getName() +
                    "  " + subSch.getDescription());
            dims = subSch.getDimensions();
            List measList=subSch.getMeasures();
            Iterator objIter = dims.iterator();

            // For each dimension in the list...
            MdmPrimaryDimension mdmPDim = null;
            while (objIter.hasNext()) {
                mdmPDim = (MdmPrimaryDimension) objIter.next();
                System.out.println(
                        "+++++++++++++++++++++++++++/n/tDimensionName: "

+
                        mdmPDim.getName() +
                        "   " + mdmPDim.getDescription());
                showDimensionInfo(mdmPDim,measList);
                System.out.println("---------------------------/n");

//SHAWT_TIME
            }
        }
    }

    public void showDimensionInfo(MdmPrimaryDimension dim,List measList)

throws Exception {
        List hiers = dim.getHierarchies();
        Iterator hiersItr = hiers.iterator();
        MdmLevelHierarchy mdmLevelHier = (MdmLevelHierarchy) dim.
                                         getDefaultHierarchy();
        System.out.println("Hier Name: " + mdmLevelHier.getName());
        // Get the parent relation from the hierarchy.
        MdmAttribute mdmParentAttr = mdmLevelHier.getParentAttribute();
        Source parentAttrSrc = mdmParentAttr.getSource();
        Source hierSrc = mdmLevelHier.getSource();
        // Reverse the parent relation to get a children relation.
        Source hierChildrenSrc = hierSrc.join(parentAttrSrc,

hierSrc.value());
        List mdmLevels = mdmLevelHier.getLevels();
        Iterator levelItr = mdmLevels.iterator();
        MdmLevel mdmLevel = null;

        //display levels
        MdmLevel mdmTopLevel = null;
        int kk = 1;
        while (levelItr.hasNext()) {
            mdmLevel = (MdmLevel) levelItr.next();
            kk++;
            if (mdmTopLevel == null && kk == 2) //get the topmost level
                mdmTopLevel = mdmLevel;
            System.out.println("/t/tLevelName: " + mdmLevel.getName() + "

 " +
                               mdmLevel.getDescription());
        }
        System.out.println("/t/tDimension Members:");
        Source topLevelSrc = mdmTopLevel.getSource();
        int pos = 1;
        //first Level's element itself, that is: total element
        Source topLevelElemSrc = topLevelSrc; //.at(pos);

        Source topLevelElemChildrenSrc = hierChildrenSrc.join(hierSrc,
                topLevelElemSrc);
        Source topLevelElemChildItemSrc=hierChildrenSrc.at(1);

        commitTrans();
        displayResult(topLevelElemSrc, true, 3);
        displayResult(topLevelElemChildrenSrc, true, 4);

    }

    private void displayResult(Source source, boolean displayLocVal, int

tabs) {
        //   try{
        CursorManagerSpecification cursorMngrSpec =
                dp.createCursorManagerSpecification(source);
        SpecifiedCursorManager cursorManager =
                dp.createCursorManager(cursorMngrSpec);
//            CursorManager cursorManager

=dp.createCursorManager(source);

        Cursor cursor = cursorManager.createCursor();

        cpw.printCursor(cursor, displayLocVal, tabs);

        // Close the CursorManager.
        cursorManager.close();
//        }catch(Exception ex){

//        }
    }

    private void commitTrans() {
        try {
            tp.prepareCurrentTransaction();
        } catch (Exception e) {
            e.printStackTrace();
        }
        tp.commitCurrentTransaction();
    }
}