SQL Clone
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
SSCommitted
SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)

Group: General Forum Members
Points: 1811 Visits: 2069
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-Forever
SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)

Group: General Forum Members
Points: 40182 Visits: 38567
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
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1202 Visits: 2001
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
SSCommitted
SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)

Group: General Forum Members
Points: 1811 Visits: 2069
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
Mr or Mrs. 500
Mr or Mrs. 500 (523 reputation)Mr or Mrs. 500 (523 reputation)Mr or Mrs. 500 (523 reputation)Mr or Mrs. 500 (523 reputation)Mr or Mrs. 500 (523 reputation)Mr or Mrs. 500 (523 reputation)Mr or Mrs. 500 (523 reputation)Mr or Mrs. 500 (523 reputation)

Group: General Forum Members
Points: 523 Visits: 2161
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 Eights!
SSC Eights! (812 reputation)SSC Eights! (812 reputation)SSC Eights! (812 reputation)SSC Eights! (812 reputation)SSC Eights! (812 reputation)SSC Eights! (812 reputation)SSC Eights! (812 reputation)SSC Eights! (812 reputation)

Group: General Forum Members
Points: 812 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
Grasshopper
Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)

Group: General Forum Members
Points: 15 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