PDA

View Full Version : Need Help with Creating query and so on



JJDTE
04-01-2008, 11:07 AM
Hey Guys,

I have big Problems with creating a query and a Schema...

I "installed" modrian with the example DB and so on. This works great.

But now I want to make my own query with my own Tables and so on. But I´m a totaly noob. So I need ur help!

I have n Tables which look like this

Tablename=tab1

ID Value1 Value2 ......
1 5 8
2 6 9
3 7 10


Table2 looks like table one but has other values.

Now I want to make a query which will compare Value 1 of all Tables and show this in the Graph. X-Achse is the id and y-ache the Value of the Table.

But how to do this??? I think for you this is really easy but i´m totally a noob. Thats why I need an example like this to understand how this works.

What I need is now the Schema and the query for that Issue. Is someone here so polite and will schow me how the query and schema has to look like. With a smal explanation pls??

Problem is, that I need this till tomorow. Time atm 5 p.m.

So pls help me and show me how to solve this easy problem.

bugg_tb
04-01-2008, 11:14 AM
My advice would be next time be more prepared. If you have to do it in Mondrian, check out the demos in the PCI, especially the Steel Wheels Jpivot stuff and also try reading http://www.databasejournal.com/features/mssql/article.php/1495511 and related articles.

Being honest though I recon you've got no chance.

But good luck

Tom

JJDTE
04-01-2008, 11:19 AM
Ok, thx 4 the Link. :)

I know it will be hard 4 me. But I also hope to solve this problem.

Can u make a query and a schema for my Problem I posted here? I think for someone who knows how to do that it should not be a problem. PLs. This is an example for me with with I can learn and so on.

Can u make a qury and a shema for that pls? :)

pls

I really need it. So pls help me. Ps. I have a oracle DB

I think the query I can do alone, hope so, but with the shema I can´t understand anything... so it would be great if u would make this example for me. Because all othe examples I found are so big and I donßt understand a word. So a small example like this will help me. So pls :)

JJDTE
04-01-2008, 02:28 PM
*bump*

Still the problem. I read the doc but still no plan how to do that.

So pls help me. :) I need it really....

bugg_tb
04-01-2008, 02:42 PM
Firstly I can't believe you bumped it, secondly if you want peoples help they'll be far more inclined to give that help if it looks like you've at least tried to make an effort and we aren't doing all the work for you.

If you boss is giving you deadlines like this on systems you don't know perhaps he should out source it, on the other hand if you've left it until the last minute then well perhaps you shouldn't have.
Tom

Phantal
04-01-2008, 02:45 PM
"Hi, I have no idea what I'm doing and I've committed to a project I need done in 2 hours, will someone do it for me so I can get paid?"

-Brian

JJDTE
04-01-2008, 03:25 PM
Hmm, I do this noch for my work...

And I will not paid for that. I´m a Student who have to do a presantation. Tomorrow I have to prasentate what mondrian can to my studends. If the presentation is good we will make a big project about this.

My problem is now, that i thouht this is much easier. But it isn´t.

So I need now ur help to check the thema. Thats why I only need a little programm. Compare some things from tables....


It´s not so that I did nothing..but I understnd not what to do. I installed the test DB and looked the schema and querys read the doc and so on...but still no plan.

Thats why I asked hier for help. I think 4 you this little thing should not be a problem. And with this little programm I have a examle wich I maybe can understand....

:D Now I understand why u won´t help me. But I will not be paid for this :). I´m only a student.

guzaldon
04-01-2008, 03:43 PM
JJDTE:

you'll probably have to use some "suplemental" tables to catupure the information you want to probably using kettle, "pentaho data intergration" to do some transformation so that you can systimatically get the differences and have data to chart and fit it into your monderian schema.

Those are my first throughts on it. So for a noob getting started with kettle wouldn't hurt it's one of the pieces that's great to get firmiliar with.

-- Nic

JJDTE
04-01-2008, 04:06 PM
hmm, ok thx.

So if i have only 1 table and I only want to show 1 value in the graph.

x-achse the ids and y-achse Value.

How my schema have to look like and my query? Can u give here a explanation?

Sry Guys, I know I´m stupid but I really did not understand the subject schema..

guzaldon
04-01-2008, 04:44 PM
Then maybe it's time to take a step back and get other concepts under your hat.
When i started working with pentaho a few years ago took me 4 to 9 months to get of the learning curves. So I felt stupid for a long time so don't get down on yourself.
But I also came from NOT a DB/developer role as well.

you got tab1 that has values and stuff then keep use kettle to turn tab1 with id and value 1 and value 2 then transformat that have the id+value to reflect with the changes so that you have simplified your efforts.

USE THE KISS MENTHODOLOGY
K.eep
I.t
S.imple
S.tupid
so when you start complicating things and are stumped step back think of how you want to brake out your steps to simple steps instead of a big complicated step.

Hope that helps point you in a direction.
More details might be needed and it might not hurt to see what you have already came up with.

JJDTE
04-02-2008, 02:34 AM
Ok I have Table 1:

id value1 value2 value3......
1 5 7 8 9
2 7 8 9 10
3 78 88 99 98
....

and so on.

Now I only want to show this table!

The ID on the x-Achse and the values on y

So that I have a Graph which will show me the values on each ID...

But don´t know how to do that. So pls give me an example for that. Only for that sitation. PLS! I really need it and I don´t understand anything with the schema.

So pls can u show me how a schema should look for this easy example.

Only to give out the table with his n-IDs and m-Values...

So pls an example how to do that... for you this should not be a problem :) So help me to understand how this work with this little example.

bugg_tb
04-02-2008, 03:52 AM
I'm still struggling to understand which part of 'show us what you've attempted and we'll show you where you've gone wrong' YOU don't understand.

Tom

JJDTE
04-02-2008, 04:15 AM
<Schema name="FoodMart">

<Cube name="Sales">

<Table name="days"/>
<Dimension name="CPU" foreignKey="day">
<Hierarchy hasAll="true" allMemberName="All Genders" primaryKey="day">
<Table name="days"/>
<Level name="ID" column="day" uniqueMembers="true"/>
</Hierarchy>
</Dimension>


</Cube>


</Schema>

My Table is called "days" and looks like:

day value1 value2....
1 22 45
2 11 35
3 14 22
4 45 56
.
.
.

Now I only want to show this Table like this...

But I can´t get there...

Thats why need ur help!

bugg_tb
04-02-2008, 04:29 AM
See, thats more like it, now we'll be more inclined to help. Have you downloaded the cubedesigner? if not get it. Setup a cube with days as a dimension and cpu as a measure. Publish it and if it works you'll see the table in the Jpivot window in the PCI and you can click on the graph and edit it to your hearts content.


Tom

JJDTE
04-02-2008, 04:42 AM
Uff the tool is really hard to understand! Looks really complicated.

How I have to modify my schema that it fits to my table...

I only want now to show the table.

My query atm:

<jp:mondrianQuery id="query01" jdbcDriver="oracle.jdbc.driver.OracleDriver" jdbcUrl="jdbc:oracle:thin:DBSNMP/admin02@localhost:1521:foodmart" catalogUri="/WEB-INF/queries/FoodMart.xml">


SELECT Hierarchize(Union({[CPU].[All Genders]}, [CPU].[All Genders].Children))
ON COLUMNS,
{descendants([CPU])} ON ROWS
FROM [Sales]



But here I get an error. think thats a problem with the schema. How I have to modify my schema and query.

bugg_tb
04-02-2008, 04:47 AM
Well CPU is a measure not a dimension, if you dont have a measure you wont see any data

JJDTE
04-02-2008, 04:51 AM
hmm, can u modify my schema and query that it will fit to the table pls? Because so I run alway agaonst a wall and don´t know how to do. Can u show me how to modify?

bugg_tb
04-02-2008, 04:59 AM
<?xml version="1.0" encoding="UTF-8"?>

<Schemaname="sdf">

<Cubename="sdf">

<Tablename="table1"/>

<Dimensionname="table1.days">

<Hierarchyname="table1.days"hasAll="true"allMemberName="All table1.days">

<Tablename="table1"/>

<Levelname="table1.days"table="table1"column="days"uniqueMembers="false"/>

</Hierarchy>

</Dimension>

<Measurename="SUM of table1.cpu"column="cpu"aggregator="sum"datatype="String"formatString="#,##0"/>

</Cube>
</Schema>

That took 30 seconds in cubedesigner

JJDTE
04-02-2008, 05:17 AM
Ok, thx

I changed that now to:

<?xml version="1.0"?>
<Schema name="FoodMart">

<Cube name="Sales">

<Table name="days"/>

<Dimension name="CPU">

<Hierarchy hasAll="true" allMemberName="All Genders" primaryKey="day">

<Table name="days"/>

<Level name="ID" column="days" uniqueMembers="false"/>

</Hierarchy>

</Dimension>

<Measure name="SUM of table1.cpu" column="value1" aggregator="sum" datatype="String" formatString="#,##0"/>

</Cube>
</Schema>

But how the query should look like? Because

SELECT Hierarchize(Union({[CPU].[All Genders]}, [CPU].[All Genders].Children))
ON COLUMNS,
{descendants([CPU])} ON ROWS
FROM [Sales]


has the error:

Mondrian Error:Dimension '[CPU]' appears in more than one independent axis.



I atm just want to output the table itself


day value1 value2....
1 22 45
2 11 35
3 14 22
4 45 56
.
.
.


Sry guys but i really don´t understand the subject. Thats why I need ur help. With a little explanation. :)

bugg_tb
04-02-2008, 05:21 AM
Yeah the Time column is a dimention the CPU column is a measure!!

JJDTE
04-02-2008, 05:26 AM
Hmm this will not help me. Sry but how I said I donßt understand the subject. How I should modify the query that it will simply output my table?

bugg_tb
04-02-2008, 05:33 AM
how will it not help you, the schema I have provided will give you a table showing days vs CPU usage?
If you only have 2 columns I've given you the answer, it doesn't get any more detailed than that

Once you've got the data in the table the rest of it is down to the MDX query itself

JJDTE
04-02-2008, 05:44 AM
Thats my table:

ID value1
1 5
2 6
3 7
4 8
...
..
.



<?xml version="1.0"?>
<Schema name="FoodMart">

<Cube name="Sales">

<Table name="days"/>

<Dimension name="CPU" foreignKey="day">

<Hierarchy hasAll="true" allMemberName="All Genders" primaryKey="day">
<Table name="days"/>
<Level name="ID" column="day" uniqueMembers="true"/>
</Hierarchy>

</Dimension>
<Measure name="cpu1" column="value1" aggregator="sum" datatype="String" formatString="#,##0"/>
<CalculatedMember name="Profit" dimension="Measures" formula="[Measures].
[cpu1]">
<CalculatedMemberProperty name="FORMAT_STRING" value="$#,##0.00"/>
</CalculatedMember>
</Cube>

</Schema>

-------------------------------

select {[Measures].[cpu1]} on columns,
{[CPU]} on rows
from Sales


---------------------------------

javax.servlet.jsp.JspTagException: javax.servlet.jsp.JspException: An error occurred while evaluating custom action attribute "test" with value "${query01.result.overflowOccured}": An error occurred while getting property "result" from an instance of class com.tonbeller.jpivot.tags.OlapModelProxy (com.tonbeller.jpivot.olap.model.OlapException: mondrian.olap.MondrianException: Mondrian Error:Internal error: Error while executing query [select {[Measures].[cpu1]} ON COLUMNS, {[CPU]} ON ROWS from [Sales] ])



-------------

What is wrong? What I should modify and how??

bugg_tb
04-02-2008, 05:50 AM
eh? Whats that table below then??

JJDTE
04-02-2008, 06:02 AM
sry browser problems

JJDTE
04-02-2008, 06:03 AM
Ok was my fault :) It works to show the Table like this.

And btw. great thx to you and all other Guys helping me. This subject is really hard and it will took a long time to understand :)

So I hope u will help me also in the future :).

Ok, but now I have in my table a "All Genders" how to remove this?

bugg_tb
04-02-2008, 06:05 AM
if you are going to carry on with mdx and mondrian please read the tutorials on databasejournal that I posted below, thats how most of us learnt. And http://wiki.pentaho.org/display/PentahoDoc/Beginners+Guide+To+Mondrian+And+MDX

Tom

And what do you mean all genders?

JJDTE
04-02-2008, 06:20 AM
On the left Side of my table I have a point called so.

It is a sum of all values. But I don´t need this. can I remove that or filter it direct?

bugg_tb
04-02-2008, 06:26 AM
Well its part of the query, you need to call the dimension not .children. What is your current query?

JJDTE
04-02-2008, 07:01 AM
select {[Measures].[run1],[Measures].[run2]} on columns,
{[Time]} on rows
from Sales

An other question btw.

Is it possible to change the style of the Graph and so on?

For example the Size?

bugg_tb
04-02-2008, 07:04 AM
Graphing I have no idea, I use the jpivot stuff I'm sure its configurable in there.

JJDTE
04-02-2008, 08:54 AM
Ok, I also use jpivot stuff.

But now that is my Problem:

At the moment I use the test testpage.jsp to show my Data. But I want change the view. How this works?
Is there any Tutorial or anything else?

Or u guys will help me design the stuff on my opinion?

At first I will show the chart than under that the Table. So a simple change at first.

But how to do that?

Also I have the posibility via toolbar change the values of chart table and so on. Is this also possible to change the standard value? If yes how? :)

I have so many questions :) U Guys have an irc channel where i can chat with u? I think this will be much easier. If u want, of corse.

But I really need ur help. I need a good presentation @ University. :)

bugg_tb
04-02-2008, 08:59 AM
er, look at my signature

JJDTE
04-02-2008, 09:18 AM
ok :D

but can u say me here how I can define standard values. For example the chart. With the chartproperties I can change the values but how change this as default.

Can u say me where and how u get this information, that I can do this for the other features also.

bugg_tb
04-02-2008, 09:20 AM
Not me, I haven't got a clue.

JJDTE
04-02-2008, 09:46 AM
Change the standard value should be possible but i nowhere found settings for that.

Anyone has an Idia for that? How to change the values?

JJDTE
04-02-2008, 03:34 PM
Hmm I looked throu all files but didn´t find a cfg file or anything else which defines the standardvalue.

So is there no chance to change that? Anyone an idea?

JJDTE
04-03-2008, 02:12 AM
A last problem with the current table.

I have always always a Summury row..

All Time

All Tags

All bla....

http://www.abload.de/img/unbenanntunb.jpg

How I can remove this? So the Table will not have that.

<Table name="total_memory"/>

<Dimension name="Time" foreignKey="ID">

<Hierarchy hasAll="true" primaryKey="ID">
<Table name="total_memory"/>
<Level name="ID" column="ID" uniqueMembers="true"/>
</Hierarchy>

</Dimension>

<Measure name="run1" column="run1" aggregator="sum" datatype="String" formatString="#,##0"/>
<Measure name="run2" column="run2" aggregator="sum" datatype="String" formatString="#,##0"/>
<CalculatedMember name="Profit" dimension="Measures" formula="[Measures].
[run1]-[Measures].[run2]">
<CalculatedMemberProperty name="FORMAT_STRING" value="$#,##0.00"/>
</CalculatedMember>
</Cube>

----------------

select {[Measures].[run1],[Measures].[run2]} on columns,
{[Time]} on rows
from Sales

bugg_tb
04-03-2008, 04:09 AM
as soon as you expand your table your mdx changes, hit the mdx button in Jpivot and that is your real mdx query, show us that and we might be able to help.

Tom

JJDTE
04-03-2008, 05:15 AM
ok thx fixed that problem on my own

JJDTE
04-03-2008, 07:01 AM
So next Problem!

I copy the testpage.jsp und called it runpage.jsp

I copy all out of testpage und pasted it into runpage.

But now when I connect to the runpage I get an error

He fixed the error on this line:
<wcf:include id="include02" httpParam="query" prefix="/WEB-INF/queries/" suffix=".jsp"/>
Stacktrace:
org.apache.jasper.servlet.JspServletWrapper.handleJspException(JspServletWrapper.java:505)
org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.java:398)
org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:337)
org.apache.jasper.servlet.JspServlet.service(JspServlet.java:266)
javax.servlet.http.HttpServlet.service(HttpServlet.java:803)whats wrong now? Its the same like in the testpage.jsp?!?!