Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Figuring out Financial Package (Agresso) Schema


Figuring out Financial Package (Agresso) Schema

Author
Message
winston Smith
winston Smith
SSC Eights!
SSC Eights! (973 reputation)SSC Eights! (973 reputation)SSC Eights! (973 reputation)SSC Eights! (973 reputation)SSC Eights! (973 reputation)SSC Eights! (973 reputation)SSC Eights! (973 reputation)SSC Eights! (973 reputation)

Group: General Forum Members
Points: 973 Visits: 2040
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?
Lynn Pettis
Lynn Pettis
SSC-Insane
SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)

Group: General Forum Members
Points: 24200 Visits: 37964
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.

Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Bill Talada
Bill Talada
Old Hand
Old Hand (382 reputation)Old Hand (382 reputation)Old Hand (382 reputation)Old Hand (382 reputation)Old Hand (382 reputation)Old Hand (382 reputation)Old Hand (382 reputation)Old Hand (382 reputation)

Group: General Forum Members
Points: 382 Visits: 1813
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,,>Wink) as DistinctCount,
Min(<colname,,>Wink as MinIntUsed,
MAX(<colname,,>Wink 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
winston Smith
winston Smith
SSC Eights!
SSC Eights! (973 reputation)SSC Eights! (973 reputation)SSC Eights! (973 reputation)SSC Eights! (973 reputation)SSC Eights! (973 reputation)SSC Eights! (973 reputation)SSC Eights! (973 reputation)SSC Eights! (973 reputation)

Group: General Forum Members
Points: 973 Visits: 2040
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,,>Wink) as DistinctCount,
Min(<colname,,>Wink as MinIntUsed,
MAX(<colname,,>Wink 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!
Gosta Munktell
Gosta Munktell
SSC Veteran
SSC Veteran (223 reputation)SSC Veteran (223 reputation)SSC Veteran (223 reputation)SSC Veteran (223 reputation)SSC Veteran (223 reputation)SSC Veteran (223 reputation)SSC Veteran (223 reputation)SSC Veteran (223 reputation)

Group: General Forum Members
Points: 223 Visits: 1408
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
sqlvogel
sqlvogel
SSC-Addicted
SSC-Addicted (484 reputation)SSC-Addicted (484 reputation)SSC-Addicted (484 reputation)SSC-Addicted (484 reputation)SSC-Addicted (484 reputation)SSC-Addicted (484 reputation)SSC-Addicted (484 reputation)SSC-Addicted (484 reputation)

Group: General Forum Members
Points: 484 Visits: 3706
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!
kjetilw
kjetilw
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
Points: 1 Visits: 2
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)
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search