Db "denormalization" for easier user reporting... ideas?

Yahoo Archive
1
Is anyone using Pentaho, JasperSoft, or other open-source BI tools?

--
Brian W. Spolarich ~ Manager, Information Services ~ Advanced Photonix / Picometrix
    bspolarich@... ~ 734-864-5618 ~ www.advancedphotonix.com


-----Original Message-----
From: vantage@yahoogroups.com [mailto:vantage@yahoogroups.com] On Behalf Of Mark Wonsil
Sent: Friday, April 17, 2009 10:14 AM
To: vantage@yahoogroups.com
Subject: RE: [Vantage] Re: db "denormalization" for easier user reporting... ideas?

> the "archiving" is interesting, hadn't thought of that. I agree that pre-
> defining the extracts is probably a required element

You can currently export any BAQ on a periodic basis using the BAQ Export
Utility in System Management | Utilities.

Epicor 9 has YAM Database Replication (yet another module). This will
periodically copy data from your production database to a SQL Server
database (similar to a CorVu datamart). From there, you can write
views/stored procedures that can be setup to fill in Excel sheets,
Access/Crystal reports, etc.

Mark W.




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

Useful links for the Yahoo!Groups Vantage Board are: ( Note: You must have already linked your email address to a yahoo id to enable access. )
(1) To access the Files Section of our Yahoo!Group for Report Builder and Crystal Reports and other 'goodies', please goto: http://groups.yahoo.com/group/vantage/files/.
(2) To search through old msg's goto: http://groups.yahoo.com/group/vantage/messages
(3) To view links to Vendors that provide Vantage services goto: http://groups.yahoo.com/group/vantage/linksYahoo! Groups Links
2
Some background firstÂ…
Our CFO wants the Accounting (and eventually other groups) to "do
their own report writing". (my opinion notwithstandingÂ…)

It starts out with this statementÂ…
"Lets train the Accountants how to create reports with Crystal Reports.
We want to avoid offering extracting data into Excel as an option. Goal
is to have our controlled financial reporting to have originated from
the accounting department as well."

OkÂ…
So one idea I presented was that we create "denormalized" db
tables (named like JobInfo, GLInfo, APInfo, ARInfo), or something
similar, which eliminates the users from having to understand the
tables, and how to join them. It could be a daily extract from
Vantage, and moved off the Production Server to another offline db.
Users could then access these tables, do their grouping, summarizing,
filtering, etc. ( I knowÂ… it is guaranteed that the user will want
to correlate some kind of different data that was not anticipated,
requiring sub-reports, dynamic parameters, custom functions, etc)

To Managment, this sounds ok, when can we have it?

Note: I know this sounds a lot like CorVu (which we have), but I
don't want to use CorVu. Queries built (by users) with CorVu are
frequently innefficient, and/or wrong.

Two questions where we'd like input from other users are:
1) Do other Vantage installations use a similar strategy (described
above) to have users create their own reports? Anyone successfully push
reporting tasks out of the IT department? We'd like to hear some
experiences
2) What reporting platform are other Vantage users using for various
Financial reporting which is not sufficiently covered by Vantage
Reports? We need reports for CashReceipts, AR Aging, OpenPO's,
SupplierHistory, InvoicePriceVariance, etc.
We currently have Crystal reports developer and Crystal Reports Server.

If some folks could provide us with some of their observations, we would
very much appreciate it.

Thanks,
David
Peregrine Semiconductor
Information Technology




[Non-text portions of this message have been removed]
3
Hi,

We do sort of use corvu for this. However, what we also do is the following;

1) Use Corvu, but it could be any other method, to a nightly schedule export of predefined queries. This is two fold, create a nightly extract but also archive per day. This maybe overkill.
2) Accounts department import this information into access and basically do what they want to do with it.
3) The archiving provides for variance reporting over time.

This means the following;

1) The export queries are constant. E.g. you know what is being exported
2) Secondly the data is as consistent as it can be in terms of timing.
3) It stops the Db access via odbc to large tables
4) Security is enforced on the db

I suppose the other option could be to look at other epicor tools such as activeplanner / frx etc.

--- In vantage@yahoogroups.com, "David" <dmclaughlin@...> wrote:
>
> Some background firstÂ…
> Our CFO wants the Accounting (and eventually other groups) to "do
> their own report writing". (my opinion notwithstandingÂ…)
>
> It starts out with this statementÂ…
> "Lets train the Accountants how to create reports with Crystal Reports.
> We want to avoid offering extracting data into Excel as an option. Goal
> is to have our controlled financial reporting to have originated from
> the accounting department as well."
>
> OkÂ…
> So one idea I presented was that we create "denormalized" db
> tables (named like JobInfo, GLInfo, APInfo, ARInfo), or something
> similar, which eliminates the users from having to understand the
> tables, and how to join them. It could be a daily extract from
> Vantage, and moved off the Production Server to another offline db.
> Users could then access these tables, do their grouping, summarizing,
> filtering, etc. ( I knowÂ… it is guaranteed that the user will want
> to correlate some kind of different data that was not anticipated,
> requiring sub-reports, dynamic parameters, custom functions, etc)
>
> To Managment, this sounds ok, when can we have it?
>
> Note: I know this sounds a lot like CorVu (which we have), but I
> don't want to use CorVu. Queries built (by users) with CorVu are
> frequently innefficient, and/or wrong.
>
> Two questions where we'd like input from other users are:
> 1) Do other Vantage installations use a similar strategy (described
> above) to have users create their own reports? Anyone successfully push
> reporting tasks out of the IT department? We'd like to hear some
> experiences
> 2) What reporting platform are other Vantage users using for various
> Financial reporting which is not sufficiently covered by Vantage
> Reports? We need reports for CashReceipts, AR Aging, OpenPO's,
> SupplierHistory, InvoicePriceVariance, etc.
> We currently have Crystal reports developer and Crystal Reports Server.
>
> If some folks could provide us with some of their observations, we would
> very much appreciate it.
>
> Thanks,
> David
> Peregrine Semiconductor
> Information Technology
>
>
>
>
> [Non-text portions of this message have been removed]
>
4
the "archiving" is interesting, hadn't thought of that. I agree that pre-defining the extracts is probably a required element

thanks for the feedback
david
--- In vantage@yahoogroups.com, "brychanwilliams" <brychanwilliams@...> wrote:
>
> Hi,
>
> We do sort of use corvu for this. However, what we also do is the following;
>
> 1) Use Corvu, but it could be any other method, to a nightly schedule export of predefined queries. This is two fold, create a nightly extract but also archive per day. This maybe overkill.
> 2) Accounts department import this information into access and basically do what they want to do with it.
> 3) The archiving provides for variance reporting over time.
>
> This means the following;
>
> 1) The export queries are constant. E.g. you know what is being exported
> 2) Secondly the data is as consistent as it can be in terms of timing.
> 3) It stops the Db access via odbc to large tables
> 4) Security is enforced on the db
>
> I suppose the other option could be to look at other epicor tools such as activeplanner / frx etc.
>
> --- In vantage@yahoogroups.com, "David" <dmclaughlin@> wrote:
> >
> > Some background firstÂ…
> > Our CFO wants the Accounting (and eventually other groups) to "do
> > their own report writing". (my opinion notwithstandingÂ…)
> >
> > It starts out with this statementÂ…
> > "Lets train the Accountants how to create reports with Crystal Reports.
> > We want to avoid offering extracting data into Excel as an option. Goal
> > is to have our controlled financial reporting to have originated from
> > the accounting department as well."
> >
> > OkÂ…
> > So one idea I presented was that we create "denormalized" db
> > tables (named like JobInfo, GLInfo, APInfo, ARInfo), or something
> > similar, which eliminates the users from having to understand the
> > tables, and how to join them. It could be a daily extract from
> > Vantage, and moved off the Production Server to another offline db.
> > Users could then access these tables, do their grouping, summarizing,
> > filtering, etc. ( I knowÂ… it is guaranteed that the user will want
> > to correlate some kind of different data that was not anticipated,
> > requiring sub-reports, dynamic parameters, custom functions, etc)
> >
> > To Managment, this sounds ok, when can we have it?
> >
> > Note: I know this sounds a lot like CorVu (which we have), but I
> > don't want to use CorVu. Queries built (by users) with CorVu are
> > frequently innefficient, and/or wrong.
> >
> > Two questions where we'd like input from other users are:
> > 1) Do other Vantage installations use a similar strategy (described
> > above) to have users create their own reports? Anyone successfully push
> > reporting tasks out of the IT department? We'd like to hear some
> > experiences
> > 2) What reporting platform are other Vantage users using for various
> > Financial reporting which is not sufficiently covered by Vantage
> > Reports? We need reports for CashReceipts, AR Aging, OpenPO's,
> > SupplierHistory, InvoicePriceVariance, etc.
> > We currently have Crystal reports developer and Crystal Reports Server.
> >
> > If some folks could provide us with some of their observations, we would
> > very much appreciate it.
> >
> > Thanks,
> > David
> > Peregrine Semiconductor
> > Information Technology
> >
> >
> >
> >
> > [Non-text portions of this message have been removed]
> >
>
5
> the "archiving" is interesting, hadn't thought of that. I agree that pre-
> defining the extracts is probably a required element

You can currently export any BAQ on a periodic basis using the BAQ Export
Utility in System Management | Utilities.

Epicor 9 has YAM Database Replication (yet another module). This will
periodically copy data from your production database to a SQL Server
database (similar to a CorVu datamart). From there, you can write
views/stored procedures that can be setup to fill in Excel sheets,
Access/Crystal reports, etc.

Mark W.
6
Hi,

That does sound good....

In v8 you can setup the BAQ to export but I have this quite slow when exporting fairly large datasets, the odbc method is quicker. It just depends....


--- In vantage@yahoogroups.com, "Mark Wonsil" <mark_wonsil@...> wrote:
>
> > the "archiving" is interesting, hadn't thought of that. I agree that pre-
> > defining the extracts is probably a required element
>
> You can currently export any BAQ on a periodic basis using the BAQ Export
> Utility in System Management | Utilities.
>
> Epicor 9 has YAM Database Replication (yet another module). This will
> periodically copy data from your production database to a SQL Server
> database (similar to a CorVu datamart). From there, you can write
> views/stored procedures that can be setup to fill in Excel sheets,
> Access/Crystal reports, etc.
>
> Mark W.
>