Duplicate rows in saiku

[jranz]

Hi,

I have a mysql table similar than this:

id	i_horas	s_fecha
1	34	20160712
2	8	20160712
3	24	20160713
4	2	20160713
5	13	20160714

And I did this schema:

<Schema name="Timesheet_Test">
<Cube name="timesheet_test" visible="true" cache="true" enabled="true">
<Table name="timesheet">
</Table>
<Dimension type="StandardDimension" visible="true" highCardinality="false" name="Fecha">
<Hierarchy name="Fecha" visible="true" hasAll="true">
<Level name="Fecha" visible="true" column="s_fecha" ordinalColumn="s_fecha" type="String" uniqueMembers="true" levelType="Regular" hideMemberIf="Never">
<KeyExpression>
<SQL dialect="generic">
<![CDATA[SUBSTRING(s_fecha,1,4)]]>
</SQL>
</KeyExpression>
</Level>
</Hierarchy>
</Dimension>
<Measure name="timesheet" column="i_horas" datatype="Numeric" aggregator="sum" visible="true">
</Measure>
</Cube>
</Schema>


Using Saiku, when I try a new query to obtain an aggregate table with the total hours per year, I have a result with duplicated years, like this:

Fecha	timesheet
2016	81
2016	81
2016	81
2016	81

I would like to have only one row per year. What am I doing wrong?

Thanks in advance,

Jose.

[Yassine]

Hi ,
I have the same problem , have u find some solution , if yes please let me know.

Thank's

[Yassine]

Hi again ;
i find a temporary solution not good but it work, the idea is to create an hierarchy to each column in your schema workbench for example :

dim_date you create H1_DATE Hierarchy contains the hight level Fecha , and after that the orhers.
I u want to have a group by just for months , u must to create another Hierarchy H2_DATE contains Month label as hight_level.

H1_DATE :
Year
Month
DAY

H2_DATE :
Month
DAY
.....

It works perfeclly for me, i u have any question tell me.

Have a nice day

[jemarroyo]

Remove from your schema this part [column="s_fecha" ordinalColumn="s_fecha"]
Remove it and try it.

regards

Jorge