EDA & itypes

Data in a file based UniVerse database is typeless, all just character string. The datatypes can be defined and conversions applied in the DICT records. 

I-descriptors need to be recompiled after being adjusted but there is no effect on the data itself.

Subroutines need to be recompiled and cataloged after any changes.

thanks - follow-up questions 

not clear on above if EDA just handles after recompiling

some itypes scenarios i'm thinking about...

'simple' change

A data field/element in an itype changes

Itype  before  Name = First Name + Lastname

Itype after Name = First Name + Middle Name + Lastname

1. Will changing the itype definition force Name to be recalculated & resynced in sql? / Do you have to redefine that field in EDA?
2. What happens when you change Surname for one record – does Name change automatically

'complex' change

Itype containing a subroutine

• how do you resync all the records calling that subroutine for scenarios below – does EDA just take care of it/do you need to manually trigger something
• what's the impact if you have heaps of records in the d/b 100K->1mil +

1. What happens when the subroutine changes
2. What happens when the underlying data being called by the subroutine changes

------------------------------
Mark Mosbauer
Project Manager
Rocket Forum Shared Account
------------------------------

Mark,

To help you better understand an I-Type can also be called a V-Type and a V-Type is a virtual field. The term Virtual Field may help you better understand what they do. The Virtual Field itself does not exist in the U2 File  (or in the SQL Table). The Virtual Field is simply a dictionary definition used at runtime to use an expression (or group of expressions)  to produce a result.

So in your example the SURNAME, MIDDLENAME and LASTNAME are data  fields in the file itself or in the SQL Table if the fields have been EDA mapped into a SQL Table. 

The Virtual Field (let's call it FULLNAME) gets the values of SURNAME, MIDDLENAME and LASTNAME while your report or query is being run and combines into one result, the I-Type or V-Type field so that it appears to be an actual field. When any of the values SURNAME, MIDDLENAME or LASTNAME change then the result of FULLNAME changes automatically.  As easier comparison for you is to think of a Virtual Field in U2 like a calculated field in SQL. In the sense that a calculated field either performs some calculation on database fields to create a value that is not directly stored in the database.

If you are used to a calculated field in SQL returning a value that is not directly stored in the database, then you will understand that the I Type / V Type fields are doing the same in the U2 File. If you are used to this concept this should answer all your questions around the 'simple' and  'complex' changes you asked.

The virtual field result will be automatically recalculated when the underlying data changes, be it a simple virtual field or one using a subroutine. If the subroutine changes and produces a new result the virtual field will automatically reflect this.

From all the questions you are asking it seems as though you would benefit from a chat about EDA with your support provider or Rocket Sales Engineer. Have you tried contacting them, they are ideally placed to talk you through all the questions you have.

Regards,

Hi Jonathan, i broadly understand itype handling in universe - i'm more interested in EDA handling.

From what you are saying, sounds like you could use EDA to map an itype to a SQL calculated field?

ie if your universe field was defined like

EDA could create the SQL table to look like

CREATE TABLE dbo.Products ( ProductID int IDENTITY (1,1) NOT NULL , QtyAvailable smallint , UnitPrice money , InventoryValue AS QtyAvailable * UnitPrice );

OR alternatively you could choose for EDA to store the output of the itype & in effect EDA would create a table like

CREATE TABLE dbo.Products ( ProductID int IDENTITY (1,1) NOT NULL , QtyAvailable smallint , UnitPrice money , InventoryValue money);

In this example - EDA calculates InventoryValue & stores the output in the SQL column InventoryValue as records are created

I would assume what you can do in an Itype exceeds the functionality of SQL calculated fields , so in some cases , you'd be forced down the path of using EDA to store the output of an itype calculation - especially if you are using a subroutine.

In this scenario - I'm trying to understand what EDA does (if anything) if the logic of the itype changes.

Hope that explains it

Hi Mark, 

As a general principle, where you do your calculation, U2 or SQL is a "domain" choice. Generally, I would follow the mantra of least impact on your production system (Less risk, change control, QA, etc.).

If there's a business process already encapsulated in a field on the source system, it makes sense to use that. If there's new transformation, that is not required on the source system, do it in the SQL staging area.

If you have a SQL calculated field, derived either an expression or stored proc, it is neither more nor less capable than a U2 calculated field (I/V-type). You just need to ensure you have the right data available and can process the business rule.

Mark,

It depends on the type of virtual field you are using and the mapping type you define that determines if the results are stored in the SQL server or not.

There are 3 types of mapping you can use in the EDA mapping tool for virtual fields.

Simple – a simple virtual attribute/I-descriptor, such as A + B. These are virtual attribute formulas that are translated to expressions and SCALAR functions.

TRANS – a virtual attribute/I-descriptor that performs a TRANS operation. These are virtual attributes or Idescriptors that are mapped using TRANS or TABLE function type of mapping. TABLE function mapping is used for multiple TRANS operations.

Materialized Virtual/I-descriptor – a virtual attribute/I-descriptor that is evaluated in U2, with the result stored in the external database. If you are mapping this type of virtual attribute/I-descriptor, select DATA as the type of mapping.

Note :You cannot update U2 virtual attributes/I-descriptors. Virtual attributes/I-descriptors are evaluated by the database engine according to the formula you specify in dictionary record. Likewise, you cannot update virtual attributes/I-descriptors that you map to the external database. This applies to all types of virtual attributes/I-descriptors, including materialized ones. Do not attempt to update their values using external database tools, or you risk compromising the consistency of your data and U2 applications.

So if you mapped a SUBR type virtual field to the external database, then the external database would be updated when the record is updated within UniVerse automatically. If you didn't map the SUBR type virtual field, the SUBR virtual field would only be available in UniVerse.

I hope this answers your question.