On Mon, Mar 30, 2015 at 6:05 PM, Ossi Viljakainen <[email protected]> wrote:
> > I am trying to figure out how to get following reports: > > 1) Sales of particular product, or group of products daily / weekly / > montlhly / yearly > 2) Sales of particluar product categories daily / weekly / montlhly / > yearly > 3) Sales per customer daily / weekly / montlhly / yearly > 4) Sales to particular customer divided per product or category, daily / > weekly / montlhly / yearly > 5) Total sales daily / weekly / montlhly / yearly > We generate such reports using Pentaho BI. This requires three steps: 1. Warehousing the data into a single table. Pentaho has support for star schema, but that extends to only one join. The reports you mentioned might need only 1 adjacent table, but if you want to drill one more nested dimension (like sales by country), then it wont work. So, you will have to warehouse the data into a table first. For the reports you mentioned, your starting point will be the sale_line table and then add all nested information to that. Here is a gist [1] of an example query which does this. *Before you ask: This cannot be a view (materialized or not) because pentaho's ODBC connector* *does not recognize anything other than tables when connecting to a postgres database.* We will try to publish the data warehousing side of things as a Tryton module in the coming days. 2. Building the models and dimensions >From pentaho you can then connect to the table we created and help it learn the columns. For example identifying the geo fields (country name, state name, city name), dates, currencies etc. This is a one time process. 3. Building dashboards and reports visually Once you have the models built, you can analyze the information visually dragging and dropping the information. The reports can then be saved and the customer can reopen the report everytime with fresh data loaded. There are also crons which can send this report periodically. I am not sure if this can be packaged into a module. If there are ideas we look forward to hearing them. Alternatives: There are two projects which try to implement BI into tryton: 1. BABI by Nantic [2] 2. HEBI by B2ck [3] [1] https://gist.github.com/sharoonthomas/82e14924fb9fefa4e1e5 [2] https://bitbucket.org/nantic/trytond-babi [3] https://codereview.appspot.com/9943044/ Thanks & Regards -- -- Sharoon Thomas Openlabs Technologies & Consulting (P) Limited w: http://www.openlabs.co.in m: +1 813.793.6736 (OPEN) Extn. 200 (Request a call <https://clarity.fm/sharoonthomas>) t: @sharoonthomas - We Win When our Customers Win
