[Isomorphic]

To clarify further, if you running a more recent JDK than 1.4, you need only:

olap4j:

olap4j-xmla-1.0.0.445.jar
olap4j-1.0.0.445.jar
javacup.jar
xercesImpl.jar

Mondrian:

mondrian.jar

Interested to know why you did not seem to need olap4j-xmla et al?

[Koripella]

Somehow I tried asking this question many times but no one answered. May be I am missing some silly point -

I have done the above sample as a spike.

My findings:

If you don't have a database which is not as per OLAP standards (refer previous post by Isomorphic), its definitely going to be a pain to get the Cube Grid to work as you want.
Basic Cube Grid would be easier though because you know before hand what query would be required to populate the cube grid. Hence like Isomorphic mentioned you can write queries which fetch data in OLAP standards. I have done some sample on this (long back) and you do have some powerful functions in Oracle which do this for you. If your database doesn't support such functions and you desperately need a Basic Cube Grid, you could even construct your data with multiple queries using DMI (not sure if this would be a good approach).

Advanced Cube Grid will be very difficult to achieve with this kind of data source. I have tried it out. The simple reason being you have to understand what all facets will be queried dynamically and the complication increases when you re-arrange the facets etc. I feel achieving Advanced Cube Grid would be a pain (not impossible though).
If you are planning to create a implementation for Advanced CubeGrid, my first suggestion would be to check a smartgwt Advanced CubeGrid sample and closely observe the query that gets fired each time you expand few columns or rows, also when you try change some column facet to row etc. When you look at the queries being fired, you will get a fair idea as to how to come up with your custom queries and how to cover all the possible scenarios that could happen.

Another shortcoming I have come across when I tried to implement Advanced Cube Grid is:
It would be easier to construct such Olap dataset using UNIONS etc in the query, but I don't think such things are allowed in the datasource xml file. Hence I left it there.


Hope this helps. Feel free to share your findings (could help me too :) )!!!!

Firstly I am trying to achieve a simple cubegrid with my database which is not as per OLAP standards. I tried achieving this using cubeGrid.setData(response.getData()) inside callback function of datasource.fetchData(). But in this case, the whole data is got to the client at once - initial module load. Instead I want the other way where I can get the data whenever I scroll down, making connections to the server as and when required. I tried this for a listgrid, it works well but for cubegrid this doesnot work.

exception.ds.xml
<DataSource
ID="exception"
serverConstructor="com.cube.server.ExceptionDataSource"
mappedBeanClass="com.cube.server.ExceptionType"
>
<fields>
<field name="source" type="text"></field>
<field name="category" type="text" ></field>
<field name="subCategory" type="text" ></field>
<field name="metric" type="text" ></field>
<field name="count" type="integer" ></field>
</fields>
</DataSource>

ExceptionDataSource.java
package com.cube.server;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

import com.isomorphic.datasource.BasicDataSource;
import com.isomorphic.datasource.DSRequest;
import com.isomorphic.datasource.DSResponse;

@SuppressWarnings("serial")
public class ExceptionDataSource extends BasicDataSource {

private static String productName = "REPO";
private static Connection conn;
private ExceptionType c = null;
private List<ExceptionType> lc = new ArrayList<ExceptionType>();

public DSResponse executeFetch(DSRequest req) throws Exception {
System.out.println("I'm in server executeFetch");
List records = fetchRecords(req.getCriteria());
return new DSResponse(records);
}

private List fetchRecords (Map criteria) {
try {\
Class.forName("oracle.jdbc.driver.OracleDriver");
conn = DriverManager.getConnection("server","username","pwd");
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
}

String sql ="select sr.SOURCE_NAME, cs.CATEGORY, cs.SUB_CATEGORY, cs.COUNT " +
"from SOURCE_REFERENCE sr, PRODUCT_TYPE pt, ETL_BATCH_DETAIL ebd, " +
"FEED_STAT fs, CATEGORY_STAT cs " +
"where ebd.PRODUCT_ID = pt.PRODUCT_TYPE_ID and " +
"ebd.SOURCE_ID = sr.SOURCE_ID and " +
"ebd.BATCH_ID = fs.BATCH_ID and fs.FEED_ID = cs.FEED_ID and " +
"pt.PRODUCT_TYPE = '" + productName + "'";
PreparedStatement stmt;
try {
stmt = conn.prepareStatement(sql);

ResultSet rs = stmt.executeQuery(sql);
while(rs.next()) {
c = new ExceptionType(rs.getString(1), rs.getString(2), rs.getString(3), "Exception Count", rs.getInt(4));
lc.add(c);
}
} catch (SQLException e) {
e.printStackTrace();
}
return lc;
}
}

ExceptionType.java
package com.cube.server;

public class ExceptionType {

private String source;
private String category;
private String subCategory;
private String metric;
private int count;

public ExceptionType(String source, String category, String subCategory,
String metric, int count) {
super();
this.source = source;
this.category = category;
this.subCategory = subCategory;
this.metric = metric;
this.count = count;
}

public String getSource() {
return source;
}
public void setSource(String source) {
this.source = source;
}
public String getCategory() {
return category;
}
public void setCategory(String category) {
this.category = category;
}
public String getSubCategory() {
return subCategory;
}
public void setSubCategory(String subCategory) {
this.subCategory = subCategory;
}
public String getMetric() {
return metric;
}
public void setMetric(String metric) {
this.metric = metric;
}
public int getCount() {
return count;
}
public void setCount(int count) {
this.count = count;
}

}

Cube.java
public void onModuleLoad() {

if(SC.hasAnalytics())
{
CubeGrid cube = new CubeGrid();
cube.setWidth(500);

if(SC.hasDrawing()) {
cube.setEnableCharting(true);
}


cube.setValueProperty("count");
cube.setRowFacets("source", "category", "subCategory");
cube.setColumnFacets("metric");
cube.setShowCellContextMenus(true);
DataSource dS = DataSource.getDataSource("exception");
cube.setDataSource(dS);
cube.setAutoFetchData(true);

VLayout layout = new VLayout();
layout.addMember(cube);
layout.draw();
}
else
{
System.out.println("not ok");
}
}

The above does give me an exception at layout.addMember(cube) ---??????
The above code is not even executing my serverConstructor code, i.e, executeFetch().

Instead if I use ListGrid in place of CubeGrid and remove the facets code part from the above code, I get the listgrid seen on my screen with data in it.
What is the difference???????

Please help!

[Koripella]

Regarding the above issue,

I got the cubegrid to see on my screen with Facet and FacetValues.
The changes I did were,

Facet f = new Facet("source", "Source");
FacetValue fv = new FacetValue("sou","sou");
f.setValues(fv);
Facet f1 = new Facet("category", "Exception Category");
f1.setWidth(150);
FacetValue fv1 = new FacetValue("abc","abc");
f1.setValues(fv1);
Facet f2 = new Facet("subCategory", "Exception SubCategory");
f2.setWidth(200);
FacetValue fv2 = new FacetValue("def","def");
f2.setValues(fv2);
Facet f3 = new Facet("metric","EC");
FacetValue fv3 = new FacetValue("ExceptionCount","Exception Count");
f3.setValues(fv3);
cube.setFacets(f,f1,f2,f3);

I know the above code also must be generated from database using cube.setData(response.getData()), but for now I hardcoded these values.

But, I am unable to see the value from database, ie, the value property.
What am I missing here?

[Isomorphic]

Can you show the actual data delivered to the client, using the RPC tab in the Developer Console? That always makes it clear what's actually making it to the cube, then we don't even need your SQL query.

[Koripella]

Nothing is delivered to the client side when i am executing isc_CubeGrid_0.fetchData();
But when I execute isc_CubeGrid_0.dataSource.fetchData(); i can see my record

Request:
{
"dataSource":"exception",
"operationType":"fetch",
"data":null,
"showPrompt":true,
"oldValues":null,
"requestId":"exception$6270"
}

Response:
[
{
queueStatus:0,
isDSResponse:true,
invalidateCache:false,
status:0,
data:[
{
category:"abc",
count:12,
metric:"Exception Count",
source:"sou",
subCategory:"def"
}
]
}
]