Aggregate Function

[smithav]

Hi,

I am working on BI project using ofbiz. I have to write a query that takes the aggregate sales of particular product over range of yrs specified by the user. Can someone suggest how to write a query to do so? I am working on BI portion of ofbiz. Do i need to use groovy for this? Or will simple java do?

[BJ Freeman]

have you looked at how prices changes is handled in BI?


=========================
BJ Freeman
Strategic Power Office with Supplier Automation  <http://www.businessesnetwork.com/automation/viewforum.php?f=52>
Specialtymarket.com  <http://www.specialtymarket.com/>
Systems Integrator-- Glad to Assist

Chat  Y! messenger: bjfr33man

>smithav sent the following on 11/13/2010 9:55 PM:


> Hi,
>
> I am working on BI project using ofbiz. I have to write a query that takes
> the aggregate sales of particular product over range of yrs specified by the
> user. Can someone suggest how to write a query to do so? I am working on BI
> portion of ofbiz. Do i need to use groovy for this? Or will simple java do?

[smithav]

No, i haven't seen that. Can you tell where to observe the changes.
But my question is different. Given the SALES as fact table, DATE as dimension table, the user enters the period over which the sales performance(measured as aggregate sales) is to be analysed. How do i perform this analysis?

[BJ Freeman]

ofbiz uses the datawarehouse model for bi.
1)create enitiy for SalesDimension
  like ProductDimension
https://localhost:8443/webtools/control/ViewRelations?entityName=ProductDimension
2)create mini code, ECAS, Services that work the enitities like in
applications\order\script\org\ofbiz\order\olap
applications\order\servicedef\secas_olap.xml
applications\order\servicedef\services_olap.xml

This gives you your Demensions and Facts that you can then Aggregate.
you can create enitityviews or use Dynamic ones. I suggest you start
with static entityviews so you can use the webtools to see the resultant
data.
you can build you queries using EntityCondition.makeCondition then use
findlist to get results.

=========================
BJ Freeman
Strategic Power Office with Supplier Automation  <http://www.businessesnetwork.com/automation/viewforum.php?f=52>
Specialtymarket.com  <http://www.specialtymarket.com/>
Systems Integrator-- Glad to Assist

Chat  Y! messenger: bjfr33man

> smithav sent the following on 11/14/2010 8:14 AM:
> No, i haven't seen that. Can you tell where to observe the changes.
> But my question is different. Given the SALES as fact table, DATE as
> dimension table, the user enters the period over which the sales
> performance(measured as aggregate sales) is to be analysed. How do i perform
> this analysis?

[BJ Freeman]

forgot the first link
https://localhost:8443/bi/control/main



=========================
BJ Freeman
Strategic Power Office with Supplier Automation  <http://www.businessesnetwork.com/automation/viewforum.php?f=52>
Specialtymarket.com  <http://www.specialtymarket.com/>
Systems Integrator-- Glad to Assist

Chat  Y! messenger: bjfr33man


BJ Freeman sent the following on 11/14/2010 3:49 PM:

> ofbiz uses the datawarehouse model for bi.
> 1)create enitiy for SalesDimension
> like ProductDimension
> https://localhost:8443/webtools/control/ViewRelations?entityName=ProductDimension
>
> 2)create mini code, ECAS, Services that work the enitities like in
> applications\order\script\org\ofbiz\order\olap
> applications\order\servicedef\secas_olap.xml
> applications\order\servicedef\services_olap.xml
>
> This gives you your Demensions and Facts that you can then Aggregate.
> you can create enitityviews or use Dynamic ones. I suggest you start
> with static entityviews so you can use the webtools to see the resultant
> data.
> you can build you queries using EntityCondition.makeCondition then use
> findlist to get results.
>
> =========================
> BJ Freeman
> Strategic Power Office with Supplier Automation
> <http://www.businessesnetwork.com/automation/viewforum.php?f=52>
> Specialtymarket.com <http://www.specialtymarket.com/>
> Systems Integrator-- Glad to Assist
>
> Chat Y! messenger: bjfr33man
>
>> smithav sent the following on 11/14/2010 8:14 AM:
>> No, i haven't seen that. Can you tell where to observe the changes.
>> But my question is different. Given the SALES as fact table, DATE as
>> dimension table, the user enters the period over which the sales
>> performance(measured as aggregate sales) is to be analysed. How do i
>> perform
>> this analysis?
>

[smithav]

I am using the datawarehouse model BI. I am using the already existing Date and Currency dimensions. I have defined Product, Customer, Location,Slaes dimensions in addition to them. Here Sales is a fact table. It contains foreign keys of all the dimension tables along with a field called sales. I have to now write a query that either takes yrs or location as input from user and gives the aggregate sales as output for that period or particular location respectively.

[hans_bakker]

How about looking at what is already there?

have a look in: applications/order/entitydef/entitymodel_olap.xml

there the sales are already defined, perhaps you can improve on that?

why people always want to build new and not improve what is already
there?

Regards,
Hans

On Mon, 2010-11-15 at 04:04 -0800, smithav wrote:
> I am using the datawarehouse model BI. I am using the already existing Date
> and Currency dimensions. I have defined Product, Customer, Location,Slaes
> dimensions in addition to them. Here Sales is a fact table. It contains
> foreign keys of all the dimension tables along with a field called sales. I
> have to now write a query that either takes yrs or location as input from
> user and gives the aggregate sales as output for that period or particular
> location respectively.

--
Ofbiz on twitter: http://twitter.com/apache_ofbiz
Myself on twitter: http://twitter.com/hansbak
Antwebsystems.com: Quality services for competitive rates.

[smithav]

I have looked at it. I doesn't contain any query that does the aggregation. And query is to be written in Java right?

[BJ Freeman]

follow the code for doing reports in the BI.
you have add more forms to have the input for the report.



=========================
BJ Freeman
Strategic Power Office with Supplier Automation  <http://www.businessesnetwork.com/automation/viewforum.php?f=52>
Specialtymarket.com  <http://www.specialtymarket.com/>
Systems Integrator-- Glad to Assist

Chat  Y! messenger: bjfr33man

> smithav sent the following on 11/15/2010 4:04 AM:

> I am using the datawarehouse model BI. I am using the already existing Date
> and Currency dimensions. I have defined Product, Customer, Location,Slaes
> dimensions in addition to them. Here Sales is a fact table. It contains
> foreign keys of all the dimension tables along with a field called sales. I
> have to now write a query that either takes yrs or location as input from
> user and gives the aggregate sales as output for that period or particular
> location respectively.