Author Topic: how to model complex query for report specificatio  (Read 2757 times)

fausto bertoli

  • EA User
  • **
  • Posts: 61
  • Karma: +0/-0
    • View Profile
how to model complex query for report specificatio
« on: August 26, 2013, 07:51:05 pm »
we are dealing to model report sepcification within EA leveraging on the data model already imported into the project.
I would like to know if there are existing experience using EA regarding the specification of the sql query underlyning the report.
This is a very important point because, specially when there are several tables  and attributes involved and a lot  of where clauses,  the query specification in natural language is too verbose  time consuming and error prone.
Thanks in advance for you answer.
Fausto

Helmut Ortmann

  • EA User
  • **
  • Posts: 953
  • Karma: +41/-1
    • View Profile
Re: how to model complex query for report specific
« Reply #1 on: August 26, 2013, 07:56:49 pm »
Hello Fausto,

to specify a SQL I would make a view of the tables and attributes involved.

Helmut
Coaching, Training, Workshop (Addins: hoTools, Search&Replace, LineStyle)

fausto bertoli

  • EA User
  • **
  • Posts: 61
  • Karma: +0/-0
    • View Profile
Re: how to model complex query for report specific
« Reply #2 on: August 27, 2013, 04:54:09 pm »
helmut would you please clarify your suggestion?
I dont understand what you mean.
Thanks.
Fausto

Geert Bellekens

  • EA Guru
  • *****
  • Posts: 11849
  • Karma: +460/-33
  • Make EA work for YOU!
    • View Profile
    • Enterprise Architect Consultant and Value Added Reseller
Re: how to model complex query for report specific
« Reply #3 on: August 28, 2013, 04:06:24 pm »
Fausto,

We also do reporting, and this is how we document the functional analysis.

Our functional model contains a logical data model, containing all entities with their attributes and relations. (also sometimes referred to as Domain Model)

The smalled part of our reporting modelling is the ReportSet.
It contains a selection of the attributes of (usually) one or (sometimes) more entities of the logical data model.
ReportSets ensure a consistent usage and grouping of the logical entities. These ReportSets are generally implemented as views on the (reporting) database.
An example of a ReportSet could be "Person" which could then contain the name, but also the current address (which would be a different entity).
ReportSets are also related to each other (much like the functional entities are related)

These reportsets are then grouped in ReportGroups. A ReportGroup is a group of ReportSets with a specified usage (say current client details, or historical payment details).
ReportSets are then usually used by a number of reports, usually destined for the same actor (say accounting reports or operational reports, or...)

On the reports themselves we model the input parameters, and the layout of the report.

This way of working allows us the specify the necessary details on each level of the reporting model.

I hope this helps a bit.


Geert

fausto bertoli

  • EA User
  • **
  • Posts: 61
  • Karma: +0/-0
    • View Profile
Re: how to model complex query for report specific
« Reply #4 on: August 28, 2013, 05:47:38 pm »
Geert thanks a lot for your clarification, but may be my question was not clear. We are startinga  new project on Microsoft AX ERP
One of the stream aim to define and develope new custom report on that.
We want to formally specify these reporting modules.
That meas for each report we neet to define, the layout, the report parameters the user has to provide, the underlyning query to extract data from the AX database and the rule mapping to connect query attributes with the layout.
the core of my question was related to: how can we specify  the  query to extract data from the database (we have already imported into EA) to provide the right information to the developer?
Thanks again.
Fausto

Helmut Ortmann

  • EA User
  • **
  • Posts: 953
  • Karma: +41/-1
    • View Profile
Re: how to model complex query for report specific
« Reply #5 on: August 28, 2013, 06:01:38 pm »
Hello Fausto,

there are many ways to do it. Geert shows his elaborate way.

I would simply show the parts of the datamodel which is used in the queries. This implies:
  • The tables used
  • The associations which in effect leads to joins
  • The attributes used in joins
  • The attributes to output

Additional I would define a class for each report. There you can define:
  • Operation with parameters and constraint which represents the report
  • Attributes which may represent the output

I don't think that EA is a good tool to model a report layout. But you can do it with other tools and then make a link from EA to the layout.

Helmut
Coaching, Training, Workshop (Addins: hoTools, Search&Replace, LineStyle)

fausto bertoli

  • EA User
  • **
  • Posts: 61
  • Karma: +0/-0
    • View Profile
Re: how to model complex query for report specific
« Reply #6 on: August 28, 2013, 06:15:01 pm »
helmut that is exactly what we are trying to do.
We put into a diagram all the involved tables, and automatically the existing relations amobg the table are shown.
We can hide that one which are unused.
But unfortunately we don't know how to show for each table only the attribute invoved into the report specification.
How can we do that?
These are tables with plenty of attributes and it is not possible to have all of them into the diagram: the diagram become unreadable.
we follow you suggestion to create a class to specify additional information, but how can we link the class attributes to the table attributes?
Sorry for the prolongation of this thread due to our low knowledge of EA.
Fausto

Geert Bellekens

  • EA Guru
  • *****
  • Posts: 11849
  • Karma: +460/-33
  • Make EA work for YOU!
    • View Profile
    • Enterprise Architect Consultant and Value Added Reseller
Re: how to model complex query for report specific
« Reply #7 on: August 28, 2013, 06:23:59 pm »
Fausto,

You question was clear, and I explained how we do it.

Our developers know how to read the logical data model and how to find the corresponding tables and attributes.

And we have relations from
Report -> Report Group -> Report Set -> Logical Data Model Entity

On the reportset attributes we describe where it comes from (if not trivial).

You can link attributes using the Link to Element Feature option in EA.

Geert

Helmut Ortmann

  • EA User
  • **
  • Posts: 953
  • Karma: +41/-1
    • View Profile
Re: how to model complex query for report specific
« Reply #8 on: August 28, 2013, 11:14:48 pm »
Hi Fausto,

you can visualize the wanted attributes by:
Element, Feature Visibility, Attribute Visibility, Custom

Helmut
Coaching, Training, Workshop (Addins: hoTools, Search&Replace, LineStyle)

ken.norcross

  • EA User
  • **
  • Posts: 63
  • Karma: +0/-0
    • View Profile
Re: how to model complex query for report specific
« Reply #9 on: August 28, 2013, 11:51:14 pm »
Quote
...On the reports themselves we model the input parameters, and the layout of the report....

Geert

Geert, could you please elaborate on how you model a report layout? We have been struggling with this.

Geert Bellekens

  • EA Guru
  • *****
  • Posts: 11849
  • Karma: +460/-33
  • Make EA work for YOU!
    • View Profile
    • Enterprise Architect Consultant and Value Added Reseller
Re: how to model complex query for report specific
« Reply #10 on: August 29, 2013, 12:06:21 am »
Ken,

We use the simple User Interface diagrams EA offers to model the layout of both GUI's as reports.

Geert