Create a reporting data store with production data

Hi Mr Williamson,

If you have purchased LBI recently, you may have gotten BPW with the bundle, which is Lawson's data warehousing tool that does exactly what you described.

Data sources wise, you will just need to modify then in Reporting Services where you can always override the Crystal data source. Same applies for your Smart Notifications.

Hope this helps!

Help us understand a bit better. Are you simply going to copy data to another database, and then build reports? Are you implementing an OLAP solution of some kind? What prevents you from reporting from the Lawson data where it currently resides? I'm thinking there is more to your questions than meets the eye.

This is Rich Bendickson from Dashboard Gear. We provide an out of the box reporting solution that builds a reporting data store and OLAP cubes on all three suites of Lawson. You can check us out at www.dashboardgear.com, but with that being said if you want to go it on your own, Lawson has a tool called BPW that can help you build a warehouse and if you have any specific questions I would be happy to help you out with those as well.

@ mwiliamson (original poster) You said: not sure how often to refresh the data, more than once per day? The answer to this is specifically "how often do you report?" Recommendations from personal experience; 1) If you consider Smart Notifications, which I use specifically for data exceptions (ie, find errors and correct), I would keep those pointing to your live database.
2) example: For month-end reporting of standard business data, refresh your data store (data warehouse) once per month after month-end processing. This gives you a 'frozen' point-in-time set of data. Reasoning: If a business leader questions the report output, or wants a little more, you have the same (non-updated) data to work with. I had a client who updated the data store after each bi-weekly payroll. This allowed them to run a myriad of reports based on the same data, data which would have otherwise been updated by the day-to-day operations. ie/ What were sales v. payroll v. employee status for the most recent fiscal month end, etc, etc...Whereas, if they were reporting from the database, things change right after payroll runs, changing the number of employees in this location, etc....(it was a business with a high number of employee data changes).
Also, keep this in mind--you don't have to update all of the data at the same time...consider the needs of the people who want the data for reporting.
PS. That same client built the data store because they were also trying to mesh data from different sources, ie/ Lawson and a point of sale system. HTH,

-Robby

We are not new to LBI, been on it for 5 + years, My reasoning for wanting a separate reporting data store is that we have several crystal developers that create reports, we have lots of crystal parameter driven reports on the dashboards pulling data, I have had instances of a crystal users putting a drain on system resources. I am not necessarily suggesting rebuilding our existing reports just point them away from our live data.. I'm thinking I could reload the data at least once per day (early a.m. hours).

I agree with your thinking :-)
Just be sure your developers are aware that the data is not real time, but only up to yesterday's processing.
And, to the point made by 'SP' above, building some cubes, stored procedures and some more concise tables, it could help the reporting efficiency quite a bit.
I know from experience (if you're dealing with HR/PR) there's no reason to pull the entire PRDISTRIB table into a data warehouse, when your reporting only needs this year and maybe last year's data.....just something to think about.
Good luck!
-Robby

It seems to me (IMHO) that you are fighting some of the same battles that we did a while back. We went through a big evaluation of the reports. Turns out that MOST of them should have been SmartNotifications instead of reports. Or the report should have been scheduled (during non-peak times) for display later on the dashboard or bursted out.

We still have more than we'd like of on-demand reports, but we've found that the use of SN's and scheduling the reports made a significant difference here.

Greg, I agree :-)
Sounds like mwiliamson has a lot of 'power users' [we have several crystal developers that create reports] that want to report on the fly and are hitting the data during business hours.
Been there, done that!
If indeed they are 'creating' reports - perhaps they should be playing in a test system....I'm just saying....

We have several power-users here too that create reports (and it's difficult to get them to use the test system as most of their reports rely on the data being current), but the way in which they are linking their reports often needs some tweaking. They get the report to work, and several months later, our DBA is monitoring the database, and poses the question about a particularly long-running sql coming from crystalras. When we dive in, in some cases she's been able to cut the report rendering time from 45 minutes to less than 2, just by linking the report properly or writing a view and accessing the view with Crystal.