Q3

Your #1 ERwin Newsletter

Hello Readers

Focused on ERwin

CA’s ERwin R8 World Tour – CA ERwin training in your city

The race is on. Sandhill Consultants are about to launch CA ERwin R8 “World Tour” Training Sandhill have been working with R8, since it’s alpha version and we are pleased to see that as the Beta comes to a close this R8.0 release will be very stable one. This has allowed Sandhill to develop another signature “Hands on” one day class that will answer all your questions on moving to CA ERwin R8. Be the first to register for your city (By contacting Simon or Robert, see the opposite column for contact e-mail) and for being the first to register you will also receive the latest Modeling for the Business book by Steve Hoberman, Donna Burbank and Chris Bradley. Please forward this to all your ERwin colleagues, as we believe that this educational event is one that you will want to be on. As always part of this column is to inform you of the latest version of ERwin. As of this time it is V7.3.10 (build 2496). If you would like a copy of this build and you are on maintenance, please let us know. Regards Robert Lutton, Robert.lutton@sandhillconsultants .com Editor, ERwin Times Sandhill Consultants

Sandhill Consultants is announces another first. “CA

ERwin R8 World Tour Training Event”

Sandhill Consultants have developed another signature one day “Hands On” training class. Moving to ERwin R8. This class will be ready to go at the same time as CA’s releases R8 in the next quarter. This unique course brought to you by Sandhill your ERwin experts will highlight the changes introduced in Release 8: These fall into the following categories. What functionality has been changed and how What functionality had been added What functionality has been removed.

• • •

Our newsletter readers are the first to hear about our classes and as such will have the ability to contact us to register before we officially announce this World Tour to the general ERwin population, early next year Not only that, but we are going to let our clients help decide where you would like Sandhill hold this special ERwin R8 “Hands On” training class. Below you will see a list of cities we believe clients are interested in Sandhill holding this exclusive training at. If you are in that city and would like to register for this event, please contact your appropriate contract. For North America, and West, that would be [email protected]. For UK, Europe and East, your contact could be [email protected] . If you are in a city that is not represented, but you feel that there would be enough ERwin Users who would like this training, then please let us know. If there is enough demand we will take a look at holding an event there.

North American Cities Albany Columbus Dallas/ Ft. Worth Kansas City New York Portland Toronto

Boston DC

Chicago Detroit

Edmonton L.A. Ottawa San Francisco Vancouver

Hartford Minneapolis Philadelphia Seattle

UK / EMEA Cities

South Africa:

Amsterdam / Utrecht Brussels Copenhagen Edinburgh Helsinki London Stockholm Oslo Stockholm

Cape Town Johannesburg

These classes will be conducted at training centers, and or client facilities if clients would like to work with Sandhill Consultants. For additional information such as costs, and location and dates ect, please contact your contact listed above. Regards Robert

To subscribe or unsubscribe to this newsletter e-mail [email protected]

Page 1

ERwin Index Name Rule By Terry Troddyn

A question we get asked fairly regularly by our customers reads along the following lines: “We have an index naming standard requiring index names to be in the form: (Index_Type_Code)_(First 25 characters of table name)_[n] The values for Index_Type_Code can be PK (primary key index), UQ (other unique index), FK (foreign key index) or NU(other non-unique index). The numeral ‘n’ must be unique within the table but is not required for primary key indexes as there cannot be more than one of those on any table. Can I get Erwin to generate these names automatically?” The good news is that you can. However, generating these names automatically does require some knowledge of how ERwin manages physical names and of ERwin’s Macro language. ERwin’s index names are inherited from the logical key group names and it is not possible to set up a rule for the generation of those. We need to take advantage of the ability to specify naming rules for the creation of physical names for dual objects. Dual objects are objects which are represented in both sides of a combined logical/physical model and come in pairs. They include Entity/Table, Attribute/Column and Key Group/index. The dialog to specify the rules for generating physical names for the physical member of the pair is shown below with the default index naming rule indicated. To access this dialog go to Tools > Names > Model Naming Options.

ERwin Index Rule Continued...

To subscribe or unsubscribe to this newsletter e-mail [email protected]

Page 2

The default naming rules for all the dual objects tend to pick up the name of the logical partner and apply it as the physical name. However, this need not be the case. You are only limited by what data the macro language can access and that can include data in external files. The first element of the naming standard requires us to determine if the index is a primary key index, unique index, foreign key index or non unique index. The codes which ERwin uses for these are PK, AK, IF and IE respectively. Luckily ERwin has an index property which we can use and a macro (%KeyType) to access it. However, as the value of the property consists of a two character indicator of the index type and a numerical suffix (to make it unique) we need to strip off the type indicator before we can check it. To do that we take a substring of two characters of the value starting at position one. The actual code for this is: (%Substr(%KeyType,1,2)) To make this check we could use a sequence of conditional %If statements to check for the presence of each of the above codes until we find a match. An alternative is to use a Switch (Case) structure which evaluates an argument and switches to (or chooses) the appropriate action depending on the answer. To set up this switch statement we use the following check: %Switch(%Substr(%KeyType,1,2)) For each index this will evaluate (%Substr(%KeyType,1,2)) and make a choice depending on whether the answer is PK, AK, IF, or IE which are the only four possible answers. Each of these four possible answers will be represented by a different naming rule and the choice will be determined by the answer to the above evaluation. Each choice will take the form: %Choose(Answer required for this option to be chosen) {Naming rule for the specified answer} As we need to account for four possible answers the structure will look like: %Switch(%Substr(%KeyType,1,2)) {%Choose(PK) {PK index naming rule)} %Choose(IF) {FK Index naming rule} %Choose(AK) {Unique index naming rule} %Choose(IE) {Non unique index naming rule}} The naming rules consist of the client’s index type codes, a substring of the first 25 characters of the table name and the numerical suffix for all but the primary key index. A numerical value which will be unique in the table can be obtained from the Key_Type property of the index where we earlier stripped off the ERwin key code. This time the sub string needs to start at the third character (after the type code) and continue to the end of the value. To do this we will specify the start position but no length. Remember however that no suffix is required for the primary key. %Switch(%Substr(%KeyType,1,2)) {%Choose(PK) {PK_%Substr(%TableName,1,25)} %Choose(IF) {FK_%Substr(%TableName,1,25)_%Substr(%KeyType,3)} %Choose(AK) {UQ_%Substr(%TableName,1,25)_%Substr(%KeyType,3)} %Choose(IE) {NU_%Substr(%TableName,1,25)_%Substr(%KeyType,3)}} As the %Switch structure allows for a default option the last line could have read: %Default {NU_%Substr(%TableName,1,25)_%Substr(%KeyType,3)}} This would then be applied anywhere the answer was not either PK, AK or IF. Copy this rule and paste it into the dialog above in a test model where at least one of the tables has at least 10 FK indexes. (In a future release the fields for the rules will have an associated text control providing more space). If it looks like the paste has not worked try moving the cursor to the left and you will probably find your rule. Before applying this rule to a production model you need to be aware that it will be automatically be applied to every index where naming inheritance is in place. Consider the need for index name hardening before taking that step.

By Terry Troddyn Sandhill Consultants

To subscribe or unsubscribe to this newsletter e-mail [email protected]

Page 3

This issue – Erwin’s Data Warehouse Support for Master Data Management (MDM) – Joe Cullen, Sandhill Consultants Welcome to the Data Warehouse Corner. Each issue we’ll identify a business problem facing the Data Warehouse / BI data modeler and discuss solutions and implications for the ERwin modeler. In this article we’ll discuss how certain Erwin functions, typically seen in Data Warehouse implementations, can play a pivotal role in providing a base for data evolution within the MDM space, as well as the capability for extending model meta data for MDM implementations, at both the logical and physical level. This author has been working recently with a number of customers that are implementing MDM solutions to evolve transactional systems management of customer, account, and product data. Notice we said transactional systems, not data warehouse- analytics systems – MDM really hasn’t made a significant push into the delivery of solutions for data warehouse applications as yet. Most of the focus is still on the side of the operational system that delivers mission critical data and functions. For customers working in this operational space, there is a need to conform to the strict demands of the MDM implementation, at both the logical and physical level, in addition to extending the model where and how the MDM solution permits. The development team needs to be able to work with both the data as it is presented by the MDM vendor, but also conform to the process and data needs of the methods to be implemented. Two functional areas of Erwin can help – namely Data Movement mapping, and Design Layers. Let’s take a look at Design Layers firstly. Design Layer applications assist the data architect since they support the concept that data modeling can be executed to capture data requirements for the enterprise, at any level of abstraction. This abstraction can be tied to designs that will be implemented in more detailed logical terms, and the physical interpretation of the information in more applied terms (at the database level). Thus the data as it is implemented can be tied back through the defined layers to the logical and abstract definitions.

In a classic portrayal, the conceptual enterprise model (logical only) is seeded as the parent of the logical and physical operational models. This is further adapted with the introduction of the MDM model (logical and physical), with the MDM-logical layer being linked to the Application Logical Models, and the MDM-physical model representing the base MDM logical model plus any conformities brought on through model extensions. For example, the MDM model can be extended with objects like business keys, that are used by the data architect to identify in their terms the unique identifier for a customer or product. In other cases business relations ( e.g. soft keys) may be implemented as traditional Foreign Keys or represented by table lookups defined within the business services themselves. Through the use of Model Layers, in specific Link Model Source, we can establish the links at the Entity and Attribute level between objects in the Conceptual Enterprise Model and MDM Logical Model. This can be further extended to the Application Logical Model and MDM Physical Model. We will then get a better understanding at the conceptual level of the business data that is acted upon, and have traceability to the business services (defined collection of data and processes) that update the physical model data. Through the Sync with Model Source function we can identify and migrate changes to the various model abstractions that arise due to implementation specifics that are delivered at the physical level (MDM Physical Model) within each project. The Add Model Source function gives us the ability to portray the federated physical implementation, where within an SOA implementation, we have more that 1 physical database involved in supporting the application. Let’s next examine Data Movement Mapping and how it can play a role in bringing together both physical and logical model meta data. We typically experience in Data Warehouse applications the need to tie ETL data from operational source systems through the staging areas (perhaps ODS) and into the Data Warehouse where it is in turn mapped to analytics requirements to produce persistent or transient data marts. Data Movement mapping gives us the ability to translate the physical model maps from source to target system.

To subscribe or unsubscribe to this newsletter e-mail [email protected]

Page 4

Erwin’s Data Warehouse Support for Master Data Management (MDM).... Continued To satisfy this need we identify the source database tables and columns and import the source from a database, DDL file, or model using the same engine that facilitates Complete Compare. Once we have the source(s) defined we can then embark on a series of 1 to N mappings between the source and target tables and columns, and also add a Transform Comment which will typically become instructions on how to transform the data, possibly including pseudo code or structured english on how the task is to be completed. This information is lastly presented to the builders in the form of a Data Browser report, that can serve as the mapping deliverable from the SDLC.

Now within the MDM Space, this mapping function can be employed to identify the Application Logical Model objects as well, including MDM Logical Model objects that are mapped to the Physical Model. Instead of defining the source as a Database or DDL script, we’ll define it as a File Source...

MDM Model File source is defined as the MDM server...

The Import From .CSV function can be used to import the MDM Logical model file. The source for the import can be created from the Data Browser or ODBC-Crystal capability in ERwin.. Format of the CSV file to import the definitions into ERwin... /*Entity Descriptions*/,,,,,,,, 1,DWXT,0,0,DWXS,0,PARTY,, 2,DWXT,0,0,DWXS,0,ORGANIZATION,, /*Attribute Descriptions*/,,,,,,,, 1,DWXC,0,1,DWXT,0,party id,integer,Uniquely identifies a party 2,DWXC,0,1,DWXT1,0,party name,varchar(100),The party's name. 3,DWXC,0,2,DWXT,0,organization id,integer,Uniquely identifies an organization

So now that we have the MDM Logical Model defined in the Source editor we can then map the logical fields to the physical columns, giving us a solid grasp of the transition from MDM Logical model to the implemented database. Couple this with the layering concept’s ability to identify the linkages across multiple models, we now have the ability to produce an end to end picture of the data abstractions to detailed data fields to the Format of the CSV file...

Next issue: MDM metadata and how to capitalize on Erwin extensions... To subscribe or unsubscribe to this newsletter e-mail [email protected]

Page 5