osbi/saiku

Saiku Analytics - The Worlds Greatest Open Source OLAP Browser

When something doesn't work as expected, then please subscribe to the Saiku User Group list and send your doubt. If that doesn't solve your problem, then please ask for help on Saiku Dev Group.

---

Saiku Analytics

The world's greatest open source OLAP browser

---

Homepage | Saiku License | Wiki | Community | Mailing List | Chat | News

---

Saiku allows business users to explore complex data sources, using a familiar drag and drop interface and easy to understand business terminology, all within a browser. Select the data you are interested in, look at it from different perspectives, drill into the detail. Once you have your answer, save your results, share them, export them to Excel or PDF, all straight from the browser. (more)

---

Setup

Build Instructions

mvn clean install -DskipTests

mvn clean clover2:setup test clover2:aggregate clover2:clover

Update project version

To update the pom versions run:

mvn versions:set -DnewVersion=3.x.x

Then remove the backups with:

find . -name "*.versionsBackup" -type f -delete

Get Saiku License

Saiku is open source and free to use. Our default server does ship with a license server installed. To get a license you can visit https://licensing.meteorite.bi and get a FREE license which is pinned to the major release of the server. This helps us with a more accurate picture of installation numbers and deployments.

Wiki

• Saiku Wiki

Community

• Saiku Community

Bugs and Feature Requests

• GitHub Issues

Discussion List

• Saiku Dev Group
• Saiku User Group
• Stack Overflow
• Freenode IRC - Channel: ##saiku

Browser Support

We do care about it.

Latest ✔	Latest ✔	Latest ✔	Latest ✔	Latest ✔

Team

Saiku is maintained by these people and a bunch of awesome contributors.

Breno Polanski	Bruno Catão	Mark Cahill	Paul Stoellberger	Tom Barber

Contributing

Check CONTRIBUTING.md for more details. Some important information:

• To get started, sign the Contributor License Agreement.

• If you find a bug then please create an issue here.

• If you have a feature request, then please get in touch. We'd love to hear from you! Send a email for: [email protected]

History

For detailed changelog, check Releases.

License

Saiku and the Saiku UI are free software. The UI, contained in this repository, is available under the terms of the Apache License Version 2. A copy is attached for your convenience.

⬆ back to top

saiku's Issues

Wrap all List<> Enunciate returns in master object for security improvement

Currently, all the JSON returns are List<> objects and this can, in some circumstances, be a security risk. See http://flask.pocoo.org/docs/security/#json-security.

Additionally, Enunciate does not document datatypes properly anyhow so it's good to do this as well.

Posting properties to a query that doesn't exist returns 200 OK

While the message is "INTERNAL_SERVER_ERROR" and the JSON doesn't parse, the HTTP status code is still 200. This fires the success callback in jQuery.

Pentaho schema permission not working inside plugin

When using Pentaho permissions for schemas (one different schema for each user), all the users can see all the schema inside Saiku plugin. The same do no happen in Pentaho's Analysis View.

Connections to non-default MySQL ports fails - Upgrade MySQL Connector/J

The version of the MySQL JDBC driver bundled with Saiku ignores the TCP port in the connection string and always connects to port 3306. Replacing the driver with 5.1.17 fixes the problem.

MDX for filter

the filter axis is using hierarchyConsistent and crossjoins for mdx
needs to be reviewed

No Cubes being displayed in Saiku-ui

This is an amazing tool, and I am tremendously excited to use it. I'm having some trouble getting everything hooked up, though. I'm running Mondrian, Saiku, and Saiku-ui on a Tomcat server on Ubuntu, with a Mysql database storing the data. I have set up a datasource/catalog for Mondrian, and can access basic MDX queries through its web interface. I have tried to set up Saiku to use the same data source (following instructions at http://projects.analytical-labs.com/knowledgebase/articles/3, adding a file to WEB-INF/classes/saiku-datasources), but am not sure if I have set it up correctly or not, because when I access the Saiku-ui, there are no Cubes available in the drop-down.

I get the following information from /saiku/rest/saiku/admin/datasources:

[{"name":"Users","properties":{"location":"jdbc:mondrian:Jdbc=jdbc:mysql://localhost/grockit?user=root&password=password;JdbcUser=root;JdbcPassword=password;JdbcDrivers=com.mysql.jdbc.Driver;Catalog=/WEB-INF/queries/Users.xml","password":"password","name":"Users","type":"OLAP","driver":"mondrian.olap4j.MondrianOlap4jDriver","username":"root"},"type":"OLAP"}]

Is there a good place to look for logs to help find debugging information, or even just to know whether or not the saiku server has successfully processed and accessed the data source?

Many thanks -- I'm very excited about using Saiku!

Provide helpful info to the user

I'm not sure if you can put textual help info into the schema, but if you can it would be nice to display this when the cube is selected.

In fact it would be nice if you could do this in some wiki style way. Allow users to annotate saved queries, and indeed cubes themselves! :)

Drive that on further, allow saving of resultsets, and you can annotate those too! Wouldnt that be cool....

Export to excel: numbers stored as text

When I export a saiku result set to excel, the numeric values in the .xls file are somehow stored incorrectly; excel says they are "number stored as text" and if I try to, say, do a sum(range) in excel, the answer is "zero".

Example excel export (with malfunctioning range sum) is attached.

Cheers,

Tyler

Dimension and measure endpoints are not returning gzip

http://admin:[email protected]/saiku/rest/saiku/admin/discover/foodmart/FoodMart/FoodMart/Sales%202/dimensions?_=1311709976233

http://admin:[email protected]/saiku/rest/saiku/admin/discover/foodmart/FoodMart/FoodMart/Sales%202/measures?_=1311709976414

Pentaho Bi-Server Plugin does not pick up mondrian.properties file

Hi,

is it possible to make the Saiku BI Server plugin to pick up the standard mondrian.properties file that is located in pentaho-solutions/system/mondrian.

Otherwise you have to keep multiple mondrian.properties in sync.

Thx,
Ingo

MDX endpoint is inconsistent

The /mdx endpoint sends application/json Content-type but returns text. Backbone can't read the returned data.

Request:
Host saiku-ui.tiemonster.cloud9ide.com
User-Agent Mozilla/5.0 (Windows NT 6.1; WOW64; rv:5.0) Gecko/20100101 Firefox/5.0
Accept application/json, text/javascript, /*; q=0.01
Accept-Language en-us,en;q=0.5
Accept-Encoding gzip, deflate
Accept-Charset ISO-8859-1,utf-8;q=0.7,;q=0.7
Connection keep-alive
X-Requested-With XMLHttpRequest
Referer http://saiku-ui.tiemonster.cloud9ide.com/

Expected response:
Date Wed, 27 Jul 2011 17:33:34 GMT
Content-Type application/json
Vary Accept-Encoding
Content-Encoding gzip
Content-Length 124
Keep-Alive timeout=15, max=91
Connection Keep-Alive

{ mdx: "SELECT
NON EMPTY {Hierarchize({[Gender].[Gender].Members})} ON COLUMNS,
NON EMPTY {Hierarchize({[Product].[Product Family].Members})} ON ROWS
FROM [Sales 2]" }

Returned response:
Date Wed, 27 Jul 2011 17:33:34 GMT
Content-Type application/json
Vary Accept-Encoding
Content-Encoding gzip
Content-Length 124
Keep-Alive timeout=15, max=91
Connection Keep-Alive

SELECT
NON EMPTY {Hierarchize({[Gender].[Gender].Members})} ON COLUMNS,
NON EMPTY {Hierarchize({[Product].[Product Family].Members})} ON ROWS
FROM [Sales 2]

Ability to reload or publish datasources without restarting the server.

It would be helpful to be able to reload add/update/delete datasources on the fly. Without having to restart saiku-server. I also noticed that the plug-in has the same issue when running under pentaho.

Thanks
Eric Smith

Warning when the number of rows is over a threshold

Add a warning when the number of rows is too high, maybe with an option to cancel the display of the data.

Add an option to configure/change that threshold.

Saiku doesn't see new schemas when the Mondrian Schema Cache is refreshed

When publishing a new schema, JPivot view can see new cubes once the Mondrian Schema Cache is refreshed via the admin console (I haven't tested it in the Pentaho web front end) but the only way to get Saiku to see a new schema is to restart the Pentaho server.

Maybe I am doing something wrong but I can't fathom this one out?

Saving a query with a name that already exists should throw a 409 Conflict

...instead of clobbering

Properties endpoint is b0rked

Instead of the properties property on the properties endpoint, this endpoint should accept arbitrary key-value pairs in the body itself

For example:

Old request
properties saiku.olap.query.automatic_execution false
saiku.olap.query.nonempty true
saiku.olap.query.nonempty.rows true
saiku.olap.query.nonempty.columns true
saiku.olap.query.drillthrough true

New request
saiku.olap.query.automatic_execution false
saiku.olap.query.nonempty true
saiku.olap.query.nonempty.rows true
saiku.olap.query.nonempty.columns true
saiku.olap.query.drillthrough true

Saiku Plugin should use the Dynamic Schema Processor from datasources.xml

Dynamic Schema Processors are powerfull means for customization. i.e. I18N, multi tenancy or dynamic roles.
Please make the Saiku Plugin aware of these processors.

http://mondrian.pentaho.com/documentation/schema.php#Schema_processor

Ability to filter on measures

Can we filter on measures? i.e. where [Measures].[count] > 10 for example.

Commonly used for filtering out scenarios where there isnt enough data to make the averages meaningful..

Also availa

Ability to add a calculated member (or member property)

Calculated members and calculated member properties that are part of just the MDX are useful for both user facing features (YTD totals) but also are an enabling piece to create a UI to do conditional formatting, outside drilling, etc.

Add ability to Saiku (and potential OLAP4J since I just looked and it doesn't appear the query model currently has the ability to do this) to create per query Calculated Members and Calculated Member properties.

servlet load on startup

for enunciate servlet

Error when opening a query

I have an error when opening a query.
When I save the query, it generates an xml file with "UTF-8" encoding.
The MDX query contained in the xml file has special characters such as "á, ñ, etc.". So when I try to open the query, it results in an error.

I realized that when I change the xml encoding to "iso-8859-1", the MDX query is opened OK.
Is it a bug?

Rewrite BasicRepositoryResource to allow pluggable persistance implementations

The restful resource BasicRepositoryResource performs saves of queries to files directly, it does not delegate off to another class. In order to embed saiku in existing applications it would be benefical to have some sort of query persistence service which it delegates to and would allow for alternative implementations to be written and plugged in (db persistance for example).

cancel olap statements

see olap4j test code:

final OlapStatement olapStatement = olapConnection.createStatement();
final Throwable[] exceptions = {null};
new Thread(
new Runnable() {
public void run() {
try {
Thread.sleep(1000);
olapStatement.cancel();
} catch (Throwable e) {
exceptions[0] = e;
}
}
}
).start();

cellsetformatter clean up

check credentials on login

use a backend call to check if the credentials are alright or not
if not -> don't login

Add conditional Formatting / annotation support

Allow for annotations similar to comments in Google Docs that allow for Dimensions, Levels, Members, and cell segments to be annotated and commented upon.

Add server side capabilities for addressing/reading/writing annotations
Come up with a limited syntax for comments (some easy to use Wiki Markup language seems like a good target)
Display the "availability" of comments on the pivot table, and a detail pane (to the right) that can show comments as a stream (or details if clicked from the pivot table)

find a better way to manage cache

see brief discussion in IRC:

codek: for the time being, you could switch of caching at schema level
it had dawned on me that we have no way to flush the cache at the mo

cool, we need that admin console asap ;)
what we should create is a rest endpoint for cache clearing, so when your ETL is complete it can hit a Saiku URL and flush the cache for you

Group and Report Totals

Add ability to query and see per group totals. Basically, in addition to displaying just the lowest level cells, retrieve the totals for other levels of granularity and display those.

application/json endpoints should not return "OK" on confirmation

Either return { "message": "OK" } or an empty body. The status code should be enough.

multiple hierarchies in selection

at the moment it is possible to select different levels from different hierarchies from a dimension
we should either disallow that or better, fix it in the backend so its ok to do so

Enable properties (for members and cells) as an optional REST point

Create and enable an optional query param for many metadata and query dataset REST endpoints that allow for showing properties or not.

Enable all the properties sections (that have otherwise been disabled/commented out) along with modify constructors of objects that get json-i-fied to ensure they aren't output if not asked for specifically and are output if asked for specifically.

Default should be for off, queryparam name should be
showProps = true/false

source version of jars + source zip + artifactory

we need 1 zip with all sources as download
the source version of the jars in an artifactory

Selection on dimension

When you try to select the members on a dimension in a query the selection is not taken into account.

Pentaho BI server 3.8 + Saiku Plugin + Firefox 6.0.2 (also IE 8 )

OLAP4J invalid query exceptions should be propogated to client

access a query via any of the endpoints (csv, json, excel) that throws an OLAP4J exception (invalid query, etc)

Should propagate the error to the client (40X error or JSON document with error information, or Redirect to JSON with error, etc).

Children of Selected Hierarchy Elements Retrieved

The children of the selected hierarchy elements are retrieved unnecessarily.

First reported in ##saiku and fixed by Paul:

(11:11:32 AM) cwarden: saiku seems to be too aggressive about descending hierarchies
(11:12:13 AM) cwarden: for example, if i have a date dimension with year, month, and day levels, and report on a measure by year, saiku queries for all of the months under each year.
(11:12:56 AM) cwarden: any way to disable this behavior?
(11:14:44 AM) pstoellberger: can you describe that in more detail?
(11:14:57 AM) pstoellberger: are you watching SQL? MDX?
(11:15:43 AM) cwarden: i'm watching the sql
(11:16:43 AM) pstoellberger: and you mean during the member cache population?
(11:17:29 AM) pstoellberger: we dont touch any lower levels in our code.... so this must be default mondrian behaviour
(11:19:50 AM) cwarden: i don't see the same behavior with jpivot.
(11:20:05 AM) pstoellberger: if you can show me some mondrian sql log about that i might be able to help you
(11:20:06 AM) cwarden: here's an example of the sql: http://pastebin.com/mQPSHtJv
(11:21:10 AM) pstoellberger: hm
(11:21:22 AM) pstoellberger: i can send you a new jar that you could try
(11:21:25 AM) pstoellberger: i think i have an idea
(11:21:46 AM) cwarden: cool, thanks.
(11:22:48 AM) pstoellberger: if its not that its not in our code base :)
(11:23:32 AM) pstoellberger: http://dl.dropbox.com/u/13927708/saiku-olap-2.2-SNAPSHOT.jar
(11:24:01 AM) pstoellberger: replace your current saiku-olap.jar with that one
(11:24:09 AM) pstoellberger: (make sure you delete/move the old one)
(11:24:24 AM) pstoellberger: tomcat/webapps/saiku/WEB-INF/lib
(11:24:57 AM) pstoellberger: or saiku/lib in the plugin
(11:38:54 AM) pstoellberger: any luck cwarden ?
(11:41:25 AM) cwarden: sorry, one sec. just being a little paranoid and checking the jar against the ci one. :)
(11:41:55 AM) thinkjson: it's a trojan, so he can look at what you're looking at
(11:41:59 AM) thinkjson: j/k
(11:42:00 AM) pstoellberger: lol
(11:42:06 AM) pstoellberger: i just commented about 10 lines of code
(11:42:21 AM) pstoellberger: you can do the changes yourself if you want :)
(11:42:37 AM) cwarden: CheatCellSetFormatter.java?
(11:43:41 AM) pstoellberger: yep
(11:44:16 AM) pstoellberger: line 535-551
(11:44:28 AM) pstoellberger: thats really paranoid :P
(11:44:56 AM) cwarden: half paranoia. half trying to learn a little about the architecture.
(11:45:07 AM) cwarden: anyway, you fixed it
(11:45:11 AM) cwarden: thanks!
(11:45:16 AM) pstoellberger: so it helped?
(11:45:26 AM) pstoellberger: hm yeah i knew that we do lots of stuff we dont have to
(11:45:47 AM) pstoellberger: in the code of those formatters might be dragons
(11:46:04 AM) pstoellberger: rewriting them is on my todo :)

http://analytical-labs.com/0001-Don-t-automatically-retrieve-children.patch

Building queries with dimensions with multiple hierarchies

Hello,
I'm having some problems when I select a member in a parent-child hierarchie with some ancestors.
For example:
If I build by my own query: "SELECT
NON EMPTY {Hierarchize({[Dim1].[member1]})} ON COLUMNS,
NON EMPTY {Hierarchize({{[Dim2].[All Dim2]},{[Parent1].[Child1]},{[Parent1].[Child1].[Child2]},{[Parent1].[Child1].[Child2].[Child3]}})} ON ROWS
FROM [Horas]"
this works perfectly and shows all hierarchies with all Totals.

But if I try to do this in a new query in saiku, selecting in columns member1 and in rows Child3 respectively, this throws an exception like this:

"org.olap4j.OlapException: Unable to find a member with name [[Parent1],[Child2],[Child3]]"
As you can see, it try to build a query skipping some members in the hierarchie.

I guess that this is a bug.

mdx explain plan in admin console

mondrian 3.3 explain plan

Wrap XML endpoints for application/json content-negotiation

When the user requests an XML endpoint with an Accept of application/json, the XML should be wrapped like this:

{ "xml": " ... " }

As far as I can tell, this affects:

GET /{user}/query/{query_name}/xml
GET /{user}/repository/{query_name}

template for serverdocs html webpage

the current layout makes the width of the list too small, so you cant really properly read the REST urls

Internazionalization

Saiku should be able to pass back the Locale to the Mondrian / XMLA Server when switching languages

Acces to a query restricted

The probleme is when you save a query with a login like admin, a
different

person with diferrent login can acces to the query who were saved by admin login

Distributed Cache

many nodes, same cache, for scaling etc

Roles

maven 3 compliant

build seems to have troubles with maven3
especially with (http://download.java.net/maven/)

Parameters to query result requests

I would like to pass parameters in the request URL to be included in the MDX query. I'm not sure what the interface looks like, but conceptually, I'd like to be able to do something like /saiku/rest/saiku/user/query/(id)/result?gender=male and /saiku/rest/saiku/user/query/(id)/result?gender=female.

Calcualted Members in virtual cube are not picked up

I have a virtual cube definition with calculated members. These are not displayed in the query's measures list.

Pentaho Platform Plugin - Do Auditing!

See here:

http://jira.pentaho.com/browse/BISERVER-6264

I'd just like to see the usual audit logs, i.e. who time and what.

"What" would be:

1. Launched Saiku
2. Ran Saiku query.
3. Saved Query
4. Loaded Query

That'd be cool. I Dont think anything more detailed than that is required, what do others think?

biserver plugin should be able to use custom connections

additionally to the datasources.xml you should be able to define custom datasources, just like in the standalone version

Administration endpoints

-datasources
-refresh connection
-clear Mondrian cache

-etc

Output columns not ordered correctly

In the output table the columns order doesn't follow what specified in the columns definition.
If you look at the attached image you can clearly see that the Units ordered measure must precede the
Units shipped measure but in the output table it doesn't happen.