maheswari

Hi all, Am newbie to OLAP. Am using Mondrian version 3.1.1.12687. I want to convert the below SQL to OLAP Schema. I dont know how to do more than one join by using <Join> tag. SELECT mdept.dept_name FROM sales sales INNER JOIN dept dept ON sales.store_id = dept.store_id AND sales.dept_no = dept.dept_no AND sales.is_fuel = dept.is_fuel INNER JOIN mod_dept_names mdept ON dept.dept_nm = mdept.dept_nm I tried like this: ****************** <Schema name="openreports"> <Dimension name="Dept"> <Hierarchy hasAll="true" allMemberName="All Dept" primaryKey="dept_no" primaryKeyTable="dept"> <Join leftKey="dept_nm" rightKey="dept_nm"> <Table name="dept"/> <Table name="mod_dept_names" /> </Join> <Level name="Dept Name" table="mod_dept_names" column="dept_name"/> </Hierarchy> </Dimension> <Cube name="Sales"> <Table name="sales"> <DimensionUsage name="Dept" source="Dept" foreignKey="dept_no"/> <Measure name="Gross" column="gross" aggregator="sum"/> <Measure name="Qty" column="qty" aggregator="sum"/> </Cube> </Schema> MDX Query: *************** select {[Measures].[Gross],[Measures].[Qty]} on COLUMNS, {([Dept].[All Dept])} on ROWS FROM [sales] But sales unit is appearing wrong, as i could not join "sales" store_id & "dept" store_id. if i try ********* <Hierarchy hasAll="true" allMemberName="All Dept" primaryKey="dept_no" primaryKeyTable="dept"> <Join leftAlias="sales" leftKey="store_id" rightAlias="dept" rightKey="store_id"> <Table name="sales" alias="sales"/> <Join leftAlias="dept" leftKey="dept_nm" rightAlias="mod_dept_names" rightKey="dept_nm"> <Table name="dept" alias="dept"/> <Table name="mod_dept_names" alias="mod_dept_names"/> </Join> </Join> </Hierarchy> its not at all taking "sales" & "dept" store_id join. its taking only the lowest join, here dept_nm. whatever i give join other than in the lowest level, nothing works. is this a BUG? or is there any solution for this? please help me out.

Thanks for your reply rodriguezum.!! But what am looking is, i want to create seperate dimension for month,week,day. For example, Year | Month | Week | Day | Unit Sales 2008| 10 | 44 | 27 | 100 I dont want all these things in the same hierarchy. I want it as a separate dimension. is there a way to do that?

Hello All, Am a newbie to OLAP. I created a separate dimesions for Year,Month,Week & Day. Here in Dimension Week, its taking all the weeks for all Months. Actually i wanted to take only the particular week numbers for the month. can some one help me? Code:My Schema is :***************<Schema name="openreports"><Dimension name="Store"><Hierarchy hasAll="true" allMemberName="All Store" primaryKey="id"><Table name="shop"/><Level name="Name" column="name" type="String" uniqueMembers="true"/></Hierarchy></Dimension><Dimension name="Time Year"><Hierarchy hasAll="true" allMemberName="Year" primaryKey="id"><Table name="time"/><Level name="Year" column="year" type="Numeric" uniqueMembers="true"/></Hierarchy></Dimension><Dimension name="Time Month"><Hierarchy hasAll="true" allMemberName="Month" primaryKey="id"><Table name="time"/><Level name="Month" column="month" uniqueMembers="false" type="Numeric"/></Hierarchy></Dimension><Dimension name="Time Week"><Hierarchy hasAll="true" allMemberName="Week" primaryKey="id"><Table name="time"/><Level name="Week" column="week" type="Numeric" uniqueMembers="false"/></Hierarchy></Dimension><Dimension name="Time Day"><Hierarchy hasAll="true" allMemberName="Day" primaryKey="id"><Table name="time"/><Level name="Day" column="day" uniqueMembers="false" type="Numeric"/></Hierarchy></Dimension><Cube name="Sales"><Table name="sales"/><DimensionUsage name="Store" source="Store" foreignKey="store_id"/><DimensionUsage name="Time Year" source="Time Year" foreignKey="time_id"/><DimensionUsage name="Time Month" source="Time Month" foreignKey="time_id"/><DimensionUsage name="Time Week" source="Time Week" foreignKey="time_id"/><DimensionUsage name="Time Day" source="Time Day" foreignKey="time_id"/><Measure name="Gross" column="gross" aggregator="sum"/><Measure name="Qty" column="qty" aggregator="sum"/></Cube></Schema>MDX Query is :***************select {[Measures].[Gross],[Measures].[Qty]} on COLUMNS,{([store].[All Store],[Time Year].[Time Year],[Time Month].[Time Month],[Time Week].[Time Week],[Time Day].[Time Day])} on ROWSFROM [sales]

Dear Experts, Am newbie to OLAP. Just trying to get the report for the below SQL by using OLAP. but i could not get the correct sales unit. SELECT store.name, mdept.dept_name, sum(sales.gross) as gross_sales FROM sales sales INNER JOIN dept dept ON sales.store_id = dept.store_id AND sales.dept_no = dept.dept_no AND sales.is_fuel = dept.is_fuel INNER JOIN store store ON store.id = dept.store_id INNER JOIN mod_dept_names mdept ON dept.dept_nm = mdept.dept_nm INNER JOIN time time ON sales.time_id = time.id WHERE dept.is_fuel like '%100%' AND time.week = extract(week from current_timestamp) AND time.year = extract(year from current_timestamp) AND mdept.dept_name IN ('A','B','C','D','E') AND mdept.service_type='SELF' GROUP BY store.name, mdept.dept_name OLAP Schema : ************* <?xml version="1.0"?> <Schema name="openreports"> <Dimension name="Store"> <Hierarchy hasAll="true" allMemberName="All Store" primaryKey="id"> <Table name="shop"/> <Level name="Name" column="name" type="String" uniqueMembers="true"/> </Hierarchy> </Dimension> <Cube name="Sales"> <Table name="sales"/> <DimensionUsage name="Store" source="Store" foreignKey="store_id"/> <Dimension name="Service" foreignKey="dept_no"> <Hierarchy hasAll="true" allMemberName="All Service" primaryKey="dept_no" primaryKeyTable="dept"> <Join leftKey="dept_nm" rightKey="dept_nm"> <Table name="dept"/> <Table name="mod_dept_names"/> </Join> <Level name="Dept Name" table="mod_dept_names" column="service_type"/> </Hierarchy> </Dimension> <Dimension name="Dept" foreignKey="store_id"> <Hierarchy hasAll="true" allMemberName="All Dept" primaryKey="store_id" primaryKeyTable="dept"> <Join leftAlias="dept" leftKey="dept_nm" rightAlias="mod_dept_names" rightKey="dept_nm"> <Table name="dept" alias="dept"/> <Table name="mod_dept_names" alias="mod_dept_names"/> </Join> <Level name="Dept Name" table="mod_dept_names" column="dept_name"/> </Hierarchy> </Dimension> <Measure name="Gross" column="gross" aggregator="sum"/> </Cube> <Role name="SALES DEPT"> <SchemaGrant access="none"> <CubeGrant cube="Sales" access="all"> <HierarchyGrant hierarchy="[service]" access="custom" rollupPolicy="partial"> <MemberGrant member="[service].[sELF]" access="all"/> </HierarchyGrant> <HierarchyGrant hierarchy="[Dept]" access="custom" rollupPolicy="partial"> <MemberGrant member="[Dept].[A]" access="all"/> <MemberGrant member="[Dept]." access="all"/> <MemberGrant member="[Dept].[C]" access="all"/> <MemberGrant member="[Dept].[D]" access="all"/> <MemberGrant member="[Dept].[E]" access="all"/> </HierarchyGrant> </CubeGrant> </SchemaGrant> </Role> </Schema> MDX Query with ROLE : *********************** select {[Measures].[Gross]} on columns, {([store].[All Store],[service],[Dept])} on ROWS from Sales I need a report in this model: ********************************* Store Name | Service Type | Dept | Year | Week | Date | Sales Unit | | | | | | But i tried store name,service type,Dept & Sales unit. But sales unit values are not proper. In "Dept" dimension i tried to join all. But its not working. <Dimension name="Dept" foreignKey="store_id"> <Hierarchy hasAll="true" allMemberName="All Dept" primaryKey="store_id" primaryKeyTable="dept"> <Join leftAlias="dept" leftKey="dept_no" rightAlias="sales" rightKey="dept_no"> <Table name="sales" alias="sales"/> <Join leftAlias="dept" leftKey="is_fuel" rightAlias="sales" rightKey="is_fuel"> <Table name="sales" alias="sales"/> <Join leftAlias="dept" leftKey="dept_nm" rightAlias="mod_dept_names" rightKey="dept_nm"> <Table name="dept" alias="dept"/> <Table name="mod_dept_names" alias="mod_dept_names"/> </Join> </Join> </Join> <Level name="Dept Name" table="mod_dept_names" column="dept_name"/> </Hierarchy> </Dimension> Error when i tried the above dimension : ***************************************** Caused by: java.lang.ClassCastException: mondrian.olap.MondrianDef$Join cannot be cast to mondrian.olap.MondrianDef$Relation at mondrian.rolap.RolapStar.getUniqueRelation(RolapStar.java:322) at mondrian.rolap.RolapStar.getUniqueRelation(RolapStar.java:312) at mondrian.rolap.RolapStar.getUniqueRelation(RolapStar.java:261) at mondrian.rolap.RolapCubeHierarchy.<init>(RolapCubeHierarchy.java:91) at mondrian.rolap.RolapCubeDimension.<init>(RolapCubeDimension.java:49) at mondrian.rolap.RolapCube.getOrCreateDimension(RolapCube.java:613) at mondrian.rolap.RolapCube.<init>(RolapCube.java:149) at mondrian.rolap.RolapCube.<init>(RolapCube.java:179) at mondrian.rolap.RolapSchema.load(RolapSchema.java:420) at mondrian.rolap.RolapSchema.load(RolapSchema.java:314) at mondrian.rolap.RolapSchema.<init>(RolapSchema.java:232) at mondrian.rolap.RolapSchema.<init>(RolapSchema.java:72) at mondrian.rolap.RolapSchema$Pool.get(RolapSchema.java:953) at mondrian.rolap.RolapSchema$Pool.get(RolapSchema.java:755) at mondrian.rolap.RolapConnection.<init>(RolapConnection.java:176) at mondrian.rolap.RolapConnection.<init>(RolapConnection.java:106) at mondrian.olap.DriverManager.getConnection(DriverManager.java:110) at com.tonbeller.jpivot.mondrian.MondrianModel.initialize(MondrianModel.java:518) at com.tonbeller.jpivot.olap.model.OlapModelDecorator.initialize(OlapModelDecorator.java:132) at com.tonbeller.jpivot.tags.OlapModelProxy$MyState.initialize(OlapModelProxy.java:77) at com.tonbeller.jpivot.tags.StackStateManager.initializeAndShow(StackStateManager.java:76) at com.tonbeller.jpivot.tags.OlapModelProxy.initializeAndShow(OlapModelProxy.java:160) at com.tonbeller.jpivot.tags.OlapModelTag.doEndTag(OlapModelTag.java:81) at org.apache.jsp.WEB_002dINF.queries.drpinTest_jsp._jspx_meth_jp_005fmondrianQuery_005f0(drpinTest_jsp.java:140) at org.apache.jsp.WEB_002dINF.queries.drpinTest_jsp._jspService(drpinTest_jsp.java:69) ... 37 more Then i modified schema to this : ********************************** <Dimension name="Dept" foreignKey="store_id"> <Hierarchy hasAll="true" allMemberName="All Dept" primaryKey="store_id" primaryKeyTable="dept"> <Join leftAlias="dept" leftKey="dept_no" rightAlias="sales" rightKey="dept_no"> <Table name="dept" alias="dept"/> <Table name="sales" alias="sales"/> <Join leftAlias="dept" leftKey="is_fuel" rightAlias="sales" rightKey="is_fuel"> <Table name="dept" alias="dept"/> <Table name="sales" alias="sales"/> <Join leftAlias="dept" leftKey="dept_nm" rightAlias="mod_dept_names" rightKey="dept_nm"> <Table name="dept" alias="dept"/> <Table name="mod_dept_names" alias="mod_dept_names"/> </Join> </Join> </Join> <Level name="Dept Name" table="mod_dept_names" column="dept_name"/> </Hierarchy> </Dimension> Error when i tried the modified dimension : ***************************************** Caused by: mondrian.olap.MondrianException: Mondrian Error:Internal error: Table 'mod_dept_names' not found at mondrian.resource.MondrianResource$_Def0.ex(MondrianResource.java:808) at mondrian.olap.Util.newInternal(Util.java:1465) at mondrian.olap.Util.newError(Util.java:1480) at mondrian.rolap.RolapLevel.checkColumn(RolapLevel.java:371) at mondrian.rolap.RolapLevel.<init>(RolapLevel.java:115) at mondrian.rolap.RolapLevel.<init>(RolapLevel.java:294) at mondrian.rolap.RolapHierarchy.<init>(RolapHierarchy.java:194) at mondrian.rolap.RolapDimension.<init>(RolapDimension.java:115) at mondrian.rolap.RolapCube.getOrCreateDimension(RolapCube.java:606) at mondrian.rolap.RolapCube.<init>(RolapCube.java:149) at mondrian.rolap.RolapCube.<init>(RolapCube.java:179) at mondrian.rolap.RolapSchema.load(RolapSchema.java:420) at mondrian.rolap.RolapSchema.load(RolapSchema.java:314) at mondrian.rolap.RolapSchema.<init>(RolapSchema.java:232) at mondrian.rolap.RolapSchema.<init>(RolapSchema.java:72) at mondrian.rolap.RolapSchema$Pool.get(RolapSchema.java:953) at mondrian.rolap.RolapSchema$Pool.get(RolapSchema.java:755) at mondrian.rolap.RolapConnection.<init>(RolapConnection.java:176) at mondrian.rolap.RolapConnection.<init>(RolapConnection.java:106) at mondrian.olap.DriverManager.getConnection(DriverManager.java:110) at com.tonbeller.jpivot.mondrian.MondrianModel.initialize(MondrianModel.java:518) at com.tonbeller.jpivot.olap.model.OlapModelDecorator.initialize(OlapModelDecorator.java:132) at com.tonbeller.jpivot.tags.OlapModelProxy$MyState.initialize(OlapModelProxy.java:77) at com.tonbeller.jpivot.tags.StackStateManager.initializeAndShow(StackStateManager.java:76) at com.tonbeller.jpivot.tags.OlapModelProxy.initializeAndShow(OlapModelProxy.java:160) at com.tonbeller.jpivot.tags.OlapModelTag.doEndTag(OlapModelTag.java:81) at org.apache.jsp.WEB_002dINF.queries.drpinTest_jsp._jspx_meth_jp_005fmondrianQuery_005f0(drpinTest_jsp.java:140) at org.apache.jsp.WEB_002dINF.queries.drpinTest_jsp._jspService(drpinTest_jsp.java:69) ... 37 more Even i tried to use <View> in "Dept" Dimension. *********************************************** <Dimension name="Dept" foreignKey="store_id"> <Hierarchy hasAll="true" allMemberName="All Dept"> <View alias="v"> <SQL dialect="generic"> SELECT mdept.dept_name as depn FROM sales sales INNER JOIN dept dept ON sales.store_id = dept.store_id AND sales.dept_no = dept.dept_no AND sales.is_fuel = dept.is_fuel INNER JOIN shop store ON store.id = dept.store_id INNER JOIN mod_dept_names mdept ON dept.dept_nm = mdept.dept_nm INNER JOIN time time ON sales.time_id = time.id WHERE mdept.dept_name IN ('REGULAR','SPECIAL','SUPER','ULTRA','DIESEL') </SQL> </View> <Level name="Dept Name" column="depn" uniqueMembers="true"/> </Hierarchy> </Dimension> This is working. but in sales unit, values are not correct. i could not use the Dimension <View> SQL inside <Measure>. Please help me some one to get the correct sales unit.Is there any way to use the select query inside <MeasureExpression>. Otherwise what way i can get the sales unit correctly.? Am using Mondrian version of "3.1.1.12687". Code:inner join

Hello Jassaru, you can use tLogRow to view the data in the console screen.. give the main row link to tLogRow from tFileInputDemilited.. you will be able to see the data in console..

Hello Sam, can you provide more information about what you wanna do?

Hi all, please help me out to sort out this problem. i wanna get the data from XML file and i wanna load into Postgres DB. the following is the XML format file i have. as jasper ETL is supporting to iterate only one loop at a time, am running into issue. i created a separate meta data file for the following elements Root/Invoice/Detail Root/Invoice/Detail/Item Root/Invoice/Detail/Item/Qty and tried to store it in DB. but am not able to retrieve the serial id from the DB table i.e., i created a separate tables for Root/Invoice/Detail, Root/Invoice/Detail/Item, Root/Invoice/Detail/Item/Qty . when am inserting the data into table, i wanna store the "Detail" table Serial id into "Item" table and "Item" Serial ID into "Qty" table. the only common field i have for all the table is InvoiceNumber. that is the main issue. because "Detail" table have more than one row with the same Invoice Number, as Detail element is also a repeating element in the XML file. so i could not retrieve the Detail serial id by using the InvoiceNumber field, when i wanna store it for Item table. i tried to map the two inputs of tFileInputXML with different metadata. but i could not. is there any way to map the two tFileInputXML component into tMap or tPostgresqlSP.. please suggest me some idea to get out of from this. Sample XML file... <?xml version="1.0" encoding="iso-8859-1"?> <!DOCTYPE sample SYSTEM "sample.dtd"> <Root version="1.0"> <Header> <Id>1111111</Id> <Date></Date> <Time></Time> <Status attri="aaaa"/> <Sender>Company</Sender> <Receiver>Company</Receiver> <Agency>Agency</Agency> </Header> <Parties> <Supplier> <Name>Company</Name> <Address>add1</Address> <Address>add2</Address> <Address>add3</Address> <Address>add4</Address> <City>city</City> </Supplier> <Buyer> <Name attri="aaaaa" >Company</Name> <Address>add1</Address> <Address>add2</Address> <Address>add3</Address> <Address>add4</Address> <City>city</City> </Buyer> </Parties> <Invoice attr="aaaaa"> <iden>444444</iden> <date></date> <place> <Name>terminal</Name> <Address>add1</Address> <Address>add2</Address> <Address>add3</Address> <Address>add4</Address> <City></City> <State></State> </place> <Detail> <Location> <Name></Name> <Address>add1</Address> <Address>add2</Address> <Address>add3</Address> <Address>add4</Address> <City></City> <State></State> <PostalCode></PostalCode> </Location> <Date></Date> <Time/> <Item count="1"> <Id>ssssssss</Id> <Qty>4500</Qty> <Qty>460</Qty> <Qty>4500</Qty> <Qty>460</Qty> <Cost>20.7</Cost> </Item> <Item count="2"> <Id>rrrrrrrrr</Id> <Qty>4500</Qty> <Qty>460</Qty> <Qty>4500</Qty> <Qty>460</Qty> <Cost>1420.7</Cost> </Item> <Item count="3"> <Id>hhhhhhh</Id> <Qty>4500</Qty> <Qty>460</Qty> <Qty>4500</Qty> <Qty>460</Qty> <Cost>140.7</Cost> </Item> </Detail> <Detail> <Location> <Name></Name> <Address>add1</Address> <Address>add2</Address> <Address>add3</Address> <Address>add4</Address> <City></City> <State></State> <PostalCode></PostalCode> </Location> <Date></Date> <Time/> <Item count="1"> <Id>ssssssss</Id> <Qty>4500</Qty> <Qty>460</Qty> <Qty>4500</Qty> <Qty>460</Qty> <Cost>20.7</Cost> </Item> <Item count="2"> <Id>rrrrrrrrr</Id> <Qty>4500</Qty> <Qty>460</Qty> <Qty>4500</Qty> <Qty>460</Qty> <Cost>1420.7</Cost> </Item> <Item count="3"> <Id>hhhhhhh</Id> <Qty>4500</Qty> <Qty>460</Qty> <Qty>4500</Qty> <Qty>460</Qty> <Cost>140.7</Cost> </Item> </Detail><Terms> <TermsType>wwrrrrrr</TermsType> <InvoiceDueDate/> <Discount applies="No"> <AfterDiscountNetDueAmt>4560.7</AfterDiscountNetDueAmt> </Discount> </Terms> </Invoice> </Root> am using java project.... Code:xml,postgres