My datawarehouse table just contains a single date SQL column, but I want to be able to drill down using the usual year/quarter/month/day levels.
I could manually create new column using Pentaho Kettle, and then create the levels one-by-one in Pentaho Schema Workbench.
But this is such a common task (I guess everybody creating sales-related cubes has to do it), so I am sure Pentaho developed something to do it in seconds. What is the recommended way of doing it?
I don t know a built in way to do this.
Here s a nice trick I ve learned a few years back: Have a table called Time with one row per day/hour, depending on your resolution preferences.
For dates, each such row will contain:
Date (date) - 2010-09-28
Day (int) - 28
Month (int) - 9
Year (int) - 2010
And you can add as many fields as you like: Quarter, Day in week, Day from year start, Week in year, etc.
This table is pre-populated with dates. When your query requires cube like aggregations, you can join the Time table with the data-warehouse table, to create any aggregation you like.
A nice advantage of this approach is that by using outer joins in the query, you can also add empty dates to the report and not only aggregate on dates which contain values. This is an important feature if you drill down to very sparse dimensions.
Is there anyone out there that used these two technologies and could give me some comparison in the form of advantages and disadvantages of both? I m currently working with BO and I have heard that ...
I m having problem restricting a query in mdx, using except function at where clause. i need to retrieved a set of data but which not in an specific set. Then i created the next query: select {[...
There are two parameters while using RBF kernels with Support Vector Machines: C and γ. It is not known beforehand which C and γ are the best for one problem; consequently some kind of model selection ...
Is it possible for a step or transformation in Pentaho Data Integration to call itself, passing the results of the previous call as parameters/variables? My first thought was to create a loop in a ...
I have the following XUL markup: <?xml version="1.0" encoding="UTF-8"?> <?xml-stylesheet href="chrome://global/skin/" type="text/css"?> <window width="400" height="275" title="...
I am building a Mondrian Cube that shows information for a large range of dates. One of the measures for this cube is an average of a percentage value. Because some of the items in the cube should ...
I was looking for ETL tool and on google found lot about Pentaho Kettle. I also need a Data Analyzer to run on Star Schema so that business user can play around and generate any kind of report or ...
Does Pentaho s ETL system, Kettle (http://kettle.pentaho.org/) have a plugin to accept information from JMS messages? I d like to set up a job that can read messages each containing a hash, extract ...