Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Figuring out Financial Package (Agresso) Schema Expand / Collapse
Author
Message
Posted Thursday, July 3, 2014 9:44 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Today @ 4:10 AM
Points: 903, Visits: 1,869
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?
Post #1589013
Posted Thursday, July 3, 2014 11:58 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 6:24 PM
Points: 23,397, Visits: 32,246
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.



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)
Post #1589082
Posted Thursday, July 3, 2014 12:13 PM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 12:50 PM
Points: 137, Visits: 871
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
Post #1589092
Posted Thursday, July 3, 2014 1:26 PM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Today @ 4:10 AM
Points: 903, Visits: 1,869
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!
Post #1589115
Posted Friday, July 4, 2014 2:31 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Yesterday @ 2:26 AM
Points: 204, Visits: 1,336
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






Post #1589237
Posted Monday, July 7, 2014 1:51 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: 2 days ago @ 10:08 AM
Points: 448, Visits: 3,353
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!


David
Post #1590090
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse