[ mssql ] first OLAP cube

create cube

1. Choose FaceTable, and bring the related table using FK

Image

2. Get suggested measure group, Using Existing Table, Image

3. Create Measure, left the measure all selectedImage

4. select all dimensions

Image

NOTE : Physical table fields shown in picture below

Image

NOTICE :

– From Measure, there is `Count` Measures added

– From Dimensions,

Image

5. Create Date Hierarchy, DimDate always have a hierarchyImage

6. Build > Deploy cube

Browse cube

7. Browse a cube, add measure

Image

8. Slice and dice

Image

ref : developerfusion

Advertisements

java get table list and package arguments from oracle

static Connection conn;
 static Statement st;

public static void main(String args[]) throws Exception{
 DatabaseMetaData md = conn.getMetaData();
 ResultSet rs = md.getTables(null, null, "%", null);
 while (rs.next()) {
 System.out.println(rs.getString(3)); // list table name
 }

String procedureCat = "XXX0000"; // package name
 String procedureSchem = "USERNAMENAJA";
 String procedureName = "CALL";  // function

rs = md.getProcedureColumns(procedureCat, procedureSchem, procedureName, null);
  while (rs.next()) {
System.out.println("----");
System.out.println("column name: "+rs.getObject(4)); // column name
System.out.println("data type: "+new Integer(rs.getInt(6))); // data type
System.out.println("columnReturnTypeName "+rs.getString(7)); // column return type name
int dbColumnReturn = rs.getInt(5);
String procReturn = "";
System.out.println("column return type: "+dbColumnReturn); // column type
switch(dbColumnReturn)
{
case DatabaseMetaData.procedureColumnIn:
procReturn = "In";
break;
case DatabaseMetaData.procedureColumnOut:
procReturn = "Out";
break;
case DatabaseMetaData.procedureColumnInOut:
procReturn = "In/Out";
break;
case DatabaseMetaData.procedureColumnReturn:
procReturn = "return value";
break;
case DatabaseMetaData.procedureColumnResult:
procReturn = "return ResultSet";
default:
procReturn = "Unknown";
}
System.out.println("column return : " + procReturn);
}
 }

static {
 try {
 // Step 1: Load the JDBC driver.
 Class.forName("oracle.jdbc.driver.OracleDriver");
 System.out.println("Driver Loaded.");
 // Step 2: Establish the connection to the database.
 String url = "jdbc:oracle:thin:@192.168.XX.XX:1521:XE";

conn = DriverManager.getConnection(url, "username", "passwordnaja");
 System.out.println("Got Connection.");
<pre>st = conn.createStatement();
 } catch (Exception e) {
 System.err.println("Got an exception! ");
 e.printStackTrace();
 System.exit(0);
 }
 }

RESULT OF get function args

column name: null
data type: 1111
CLOB
column return type: 5
column return : return value
----
column name: IN_FUNC_NAME
data type: 12
VARCHAR2
column return type: 1
column return : In
----
column name: IN_PARAMS
data type: 12
VARCHAR2
column return type: 1
column return : In

NOTE :

1. data type(java.sql.Types)

1111 = Types.OTHER

2005 = Types.CLOB

12 = Types.VARCHAR

2. Reference for each col in getProcedureColumns() resultset

String procedureCatalog     = rs.getString(1);
String procedureSchema      = rs.getString(2);
String procedureName        = rs.getString(3);
String columnName           = rs.getString(4);
short  columnReturn         = rs.getShort(5);
int    columnDataType       = rs.getInt(6);
String columnReturnTypeName = rs.getString(7);
int    columnPrecision      = rs.getInt(8);
int    columnByteLength     = rs.getInt(9);
short  columnScale          = rs.getShort(10);
short  columnRadix          = rs.getShort(11);
short  columnNullable       = rs.getShort(12);
String columnRemarks        = rs.getString(13);

3. NOTE that, if package function return CLOB, java will return as Types.OTHER (1111 like above output ).

If we use Types.OTHER in ps.registerOutParameter(), we will face the following error.

org.seasar.framework.exception.SQLRuntimeException: [ESSR0072]SQLException(SQL=[], Message=[Invalid column type], ErrorCode=17004, SQLState=null)

We must pass Types.CLOB into ps.registerOutParameter(), not Types.OTHER.

ref : java2s, mkyong, jguruCodo