![]() | Talend Time Dimension component | ![]() |
tTimeGenerator![]() Background : In many Data Mart / Data Warehouse projects, we need a time dimension, normally with 3 levels, being Year, Quarter and Month. This dimension is used in most of the analysis and also to calculate special time rollups (YTD, QTD, MAT etc). Most of the BI consultants already have a script to generate and populate the needed tables in ORacle , MS SQL Server etc. We are here proposing a simple implementation using a single Talend component. The solution A single component can generate values for the 3 levels (Year, Quarter, Month) in a specified period. It is also possible to join the tables to create a single time dimension table if needed. No name decoding for the months has been embedded since this would depend from the language needed, however this can be easily achieved adding a csv file with the month names, such as the following :
NONTH_NUMBER,MONTH_NAME_SHORT,MONTH_NAME_LONG
1,"Jan","January" 2,"Feb","February" 3,"Mar","March" 4,"Apr","April" 5,"May","May" 6,"Jun","June" 7,"Jul","July" 8,"Aug","August" 9,"Sep","September" 10,"Oct","October" 11,"Nov","November" 12,"Dec","December" The generated tables have a fixed schema, containing the basic information, you can erich it using a tMap component and adding eventual calculated fields etc.
YEAR
Current version
QUARTER
MONTH
Version : 0.4 Release Date : Sep 11 2012 Status : Beta Example The job tTimeGenerastorDemo demomstrates the usage of the tTimeGenerator component ![]() This simple job shows how to use tTimeGenerator. This component needs only two parameters being the start and end period, specified in numeric format YYYYMMDD. To have all the months between Jan 1980 till Dec 2040, the two parameters will be 19800101 and 20401231, note that currently the component is not using the day information, therefore the two last digits can have any value. The left subjob explains how to use the component to generate 3 different tables, one for Year, one for Qaurter and one for Month.Obviously it is not mandatory to generrate all three of them. It also shows how to add other information, such as "month long name" and "month short name" in this case from a csv file. The right subjob instead uses multime tTimeGenerators and joins them in a tMap to create a single time dimension table.Generally, in BI projects you may want to have the 3 levels separated (that eases relative time calculations, prompts etc), but with Talend it is easy to use either approach (also both two if needed). Downloads License THIS SOFTWARE IS PROVIDED BY POWERUP ``AS IS'' AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE DISCLAIMED. IN NO EVENT SHALL POWERUP BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE. |