July 3, 2014 at 9:44 am
We are beginning exploration of all our systems and data, with the aim of eventually building a Data Warehouse.
All financial data is stored in the Agresso financial package.
I just got access to the Agresso sql instance and looked at the schema and its massive, having just under 3000 user tables and 400 views. Im not sure where to start on something this large.
Anyone have any experience directly with agresso, or generally navigating schemas of proprietary accounts packages?
July 3, 2014 at 11:59 am
That really isn't too bad. You should see the PeopleSoft ERP systems for Finance and HR, around 50,000 tables in each.
Doesn't include views, or other database objects.
July 3, 2014 at 12:13 pm
Like a boa constrictor, you just have to keep tightening your grip on it. I've converted hundreds of databases over the years. First I start out with row counts for all tables and I review them in descending count order. Next I define missing primary keys, alternate keys, and foreign keys. Next, convert null columns to not null where possible. Run statistics on each column to really see what is going on. Next, I define cross reference tables to convert their codes into my codes. Then I can start migrating data into my schema.
In other words: its a whole lot of work with no shortcuts.
Make some templates to do quick interrogations, one for each datatype
-- interrogate int column
-- to see if it is oversized, nullable, or standard values
select
COUNT(*) as CountRows,
COUNT(distinct(<colname,,>)) as DistinctCount,
Min(<colname,,>) as MinIntUsed,
MAX(<colname,,>) as MaxIntUsed,
sum(case when <colname,,> is null then 1 else 0 end) as NullsCount,
sum(case when <colname,,> = 0 then 1 else 0 end) as ZeroesCount,
sum(case when <colname,,> > 0 then 1 else 0 end) as PositivesCount,
sum(case when <colname,,> < 0 then 1 else 0 end) as NegativesCount
from
<tablename,,>
-- most frequent values
select top 100
<colname,,>,
COUNT(*) as frequencyCount
from
<tablename,,>
group by
<colname,,>
having
COUNT(*) > 1
order by
COUNT(*) desc
July 3, 2014 at 1:26 pm
Bill Talada (7/3/2014)
Like a boa constrictor, you just have to keep tightening your grip on it. I've converted hundreds of databases over the years. First I start out with row counts for all tables and I review them in descending count order. Next I define missing primary keys, alternate keys, and foreign keys. Next, convert null columns to not null where possible. Run statistics on each column to really see what is going on. Next, I define cross reference tables to convert their codes into my codes. Then I can start migrating data into my schema.In other words: its a whole lot of work with no shortcuts.
Make some templates to do quick interrogations, one for each datatype
-- interrogate int column
-- to see if it is oversized, nullable, or standard values
select
COUNT(*) as CountRows,
COUNT(distinct(<colname,,>)) as DistinctCount,
Min(<colname,,>) as MinIntUsed,
MAX(<colname,,>) as MaxIntUsed,
sum(case when <colname,,> is null then 1 else 0 end) as NullsCount,
sum(case when <colname,,> = 0 then 1 else 0 end) as ZeroesCount,
sum(case when <colname,,> > 0 then 1 else 0 end) as PositivesCount,
sum(case when <colname,,> < 0 then 1 else 0 end) as NegativesCount
from
<tablename,,>
-- most frequent values
select top 100
<colname,,>,
COUNT(*) as frequencyCount
from
<tablename,,>
group by
<colname,,>
having
COUNT(*) > 1
order by
COUNT(*) desc
Thanks Guys.
I think i understand the logic ( i need to try it out to get it straight in my head), but if i have it correct, I basically need to analyze column in each table and see if they have a relationship to another table (assuming the designers used constraints for referential integrity). assuming 3000 tables and 10 cols in each table (big assumption), thast 30,000 columns, which is a long time analyzing a database! Im not looking forward to this at all!
July 4, 2014 at 2:31 am
Do you mean Agresso from the Dutch Company UNIT4?
Agresso is frequently used in Sweden.
Agresso has many modules. If you start with the general ledger
there are only about four tables which are of interest like:
agltransact, aglbuddetatai, agldimvalue and aglrelvalue.
agltransact contains the transaction.
aglbuddetatail (normaly) contains the budget.
Short: the dimensions are built by relations stored in
agldimvalue and aglrelvalue. (This can be the tricky part
as the relations can be nested).
I have have a lot of experince of Agresso during almost 15 years.
🙂
GM
July 7, 2014 at 1:51 pm
winston Smith (7/3/2014)
We are beginning exploration of all our systems and data, with the aim of eventually building a Data Warehouse.
I would suggest that may be the wrong way to go about starting your data warehouse. Start by identifying a prioritised set of requirements. Do the modelling and data sourcing necessary to support those requirements, then iterate: repeat the process for the next set of priorities.
The requirements and modelling for a data warehouse should be subject-oriented and very specific to your business needs. You won't find any of that in your accounting system which will normally be extremely generic and business-agnostic. You may well find that most or all of your requirements can be supported by a small fraction of the data model in your accounting system - in which case most of the time you might have spent understanding that system would have been wasted. Finally, most enterprise accounting software vendors don't expect customers to interface directly with their underlying data model. Their approach to support and maintenance may assume you use only published interfaces and features. I would suggest those ought to be the starting point for any data acquisition effort. Good luck with your project!
August 13, 2016 at 8:56 am
Hi.
Anyone know which table I can fine the description for the field AGLRELVALUE.REL_VALUE in Agresso?
I'll be very greatfull for any input.
Regards
Kjetil (newbie)
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply