SSDN - ETL and data mapping

XML Editor

Sign Up

Search

Options

Chat

Help

News

Log in

Not Logged in

Topic

Byron Bignell

Subject: ETL and data mapping
Author: Byron Bignell
Date: 27 Feb 2006 11:54 AM

Hi,

I've been using Stylus Studio since last year when I upgraded from Tibco's Turbo XML. I've also been using the XML suite from Altova as it was provided to me by a client.

I use Stylus for 90% of my work but I find myself going back to Mapforce in order to do data mapping to and from XML to the various databases I work with. I'd really rather work in Stylus, but right now it doesn't give me the code generation capabilities that Mapforce does, nor the UI support for building transformations and mappings in the same mostly nice way that Mapforce does.

If there was one feature set that I'd love to see in Stylus it's this. A Data~XML/XML~Data mapping tool with code generation that's as good or better than Mapforce. Something with the ability to taylor the initial query that gets generated (no more wild cards as Mapforce does...and for which I find myself editing waaay too much c# code...)

There is is for what it's worth, let me know what you think.

Byron

Minollo I.

Subject: ETL and data mapping
Author: Minollo I.
Date: 27 Feb 2006 12:08 PM

Byron,
I'm sure you are aware of the Stylus Studio XQuery and XSLT Mapper editors; so, let me ask you a few questions.

- Are you saying that you find Stylus Studio Mappers less helpful than the competitive products in general or only in specific usecases? (Like, for example, storing data *into* a database)

- When you talk about code-generation, you may mean several things from a Stylus Studio point of view: Stylus Studio Mappers generate XSLT/XQuery code for you; and Stylus Studio also generates Java code to be able to execute such XSLT/XQuery scripts from a Java application or from the command line. What do you exactly mean with "code-generation" in this context, and how would you like to see Stylus Studio helping you more/differently than it does today?

Thanks for your feedback,
Minollo

Byron Bignell

Subject: ETL and data mapping
Author: Byron Bignell
Date: 27 Feb 2006 12:15 PM

Hi.

I find Mapforce easier to use for mapping data to and from xml and databases. The tools are comprehensive and I can design the logic and functions of any data trasnformations with relative ease.

In terms of code generation. Once the data mapping design is complete I use Mapforce to generate the c# code that 'is' the mapping project. It rund faster as an application than it does in the development environment and it means that I can compile and distribute/deliver the executable where needed.

Byron

Minollo I.

Subject: ETL and data mapping
Author: Minollo I.
Date: 27 Feb 2006 12:49 PM

>I find Mapforce easier to use for mapping data to and from xml and
>databases.

Can you tell us why you find it easier to use? I'm not trying to convince you that it's not true; I'm just trying to understand what we should do if we wanted to change our current design/implementation to make Mappers easier for you.

>In terms of code generation. Once the data mapping design is complete
>I use Mapforce to generate the c# code that 'is' the mapping project.
>It rund faster as an application than it does in the development
>environment and it means that I can compile and distribute/deliver
>the executable where needed.

Stylus Studio follows a similar approach, but it always relies on the XSLT/XQuery artifacts that you created during development; deployed code does run faster than when in the development environment, but it still runs the same XSLT and XQuery that you created at development time. Being strong supporters of XML technologies and standards, we believe that relying on XSLT and XQuery processors is the best way to provide flexibility and consistency with the language you manipulate at development time.

Minollo

Byron Bignell

Subject: ETL and data mapping
Author: Byron Bignell
Date: 27 Feb 2006 01:00 PM

Hi.

I find the organization of the tools in mapforce to be very good. I can move very quickly through a project. The mapforce tools are comprehensive and allow me to build custom functions that can in turn be reused project to project.

The idea of mounting the database tables and xml schema in the mapping UI and then being able to drag from one to another and then test the results while making additions and adjustments in terms of math, logical and other operators makes for a very usable tool.

Where it comes to code, I understand the xml standards perspective and all and it works, but most often I'm asked to provide c# or java code to the client, who aren't necessarily so familliar with the idea.

B

Minollo I.

Subject: ETL and data mapping
Author: Minollo I.
Date: 27 Feb 2006 01:08 PM

>The idea of mounting the database tables and xml schema in the
>mapping UI and then being able to drag from one to another and then
>test the results while making additions and adjustments in terms of
>math, logical and other operators makes for a very usable tool.

I can see that as long as database tables are used as target of transformations; I still have a few problems to understand the difference when dealing with XML Schema/XML as a taget.

>Where it comes to code, I understand the xml standards perspective
>and all and it works, but most often I'm asked to provide c# or java
>code to the client, who aren't necessarily so familliar with the idea.

I can understand that; but it's also true that, as long as the "client" is involved, the implementation details in terms of XML native languages or in terms of Java/C# shouldn't make much difference; usually you expose the result of Mapper-like tools as some kind of "XML Data Service", and the client would tend to worry about what interface that exposes, not much what code it contains. Isn't that the case for you?

Minollo

Byron Bignell

Subject: ETL and data mapping
Author: Byron Bignell
Date: 27 Feb 2006 01:26 PM

Hi,

It's often the case where the database (tables) are the target of the work I do. It usually involves taking data from non-related systems as database information and then bringing them together into a single XML document (schema). The resulting XML data is then mapped to a 'new and improved' database schema. So in the end the database (or rather one or more databases) are the source and then destination of the data and the XML/schema is a transport mechanism.

Code-wise ending up as c# or java has been the rule for me, it just seems to work out that way. More often than naught it ends up that the client wants c# and they get 'testy' if they don't get it.

Byron

Minollo I.

Subject: ETL and data mapping
Author: Minollo I.
Date: 27 Feb 2006 01:39 PM

Thanks for the additional details.

Using database tables as mapping targets will dramatically improve in Stylus Studio when Updates will be a feature of XQuery and of the underlying DataDirect XQuery engine that is specialized in RDBMS connectivity.

If you are not familiar with DataDirect XQuery, I would recommend that you take a look at http://www.stylusstudio.com/xml_videos.html (under the XQuery section) and at http://www.stylusstudio.com/xml_videos.html

Thanks for your help,
Minollo

Byron Bignell

Subject: ETL and data mapping
Author: Byron Bignell
Date: 27 Feb 2006 02:37 PM

Hi,

Excellent presentations. Learned quite a bit watching them. Yes, DB tables as targets for update/insert from XQuery will be a major improvement to Stylus. I look forward to it.

I guess that until that point I'll have to eep on keeping on with what I'm using now.

Byron

Minollo I.

Subject: ETL and data mapping
Author: Minollo I.
Date: 27 Feb 2006 02:40 PM

BTW, my second link was wrong, of course; it should have been: http://www.datadirect.com/xquery

Minollo

David Karr

Subject: RE: ETL and data mapping
Author: David Karr
Date: 01 Mar 2006 11:56 AM

> -----Original Message-----
> From: stylus-studio-feature-requests Listmanager
> [mailto:stylus-studio-feature-requests.listmanager@stylusstudio.com]
> From: "Byron Bignell"
>
> I find Mapforce easier to use for mapping data to and from
> xml and databases. The tools are comprehensive and I can
> design the logic and functions of any data trasnformations
> with relative ease.
>
> In terms of code generation. Once the data mapping design is
> complete I use Mapforce to generate the c# code that 'is' the
> mapping project. It rund faster as an application than it
> does in the development environment and it means that I can
> compile and distribute/deliver the executable where needed.

Are you executing that C# code in multiple threads on a multi-cpu box,
and is it running in a long-running server?

I found problems with thread-safety in the generated Java code from
MapForce (a static DocumentBuilder object). If I were you, I would make
sure your generated C# code doesn't have similar issues. It would be a
completely different implementation, but it's possible the two
implementations had common design principles.

Byron Bignell

Subject: RE: ETL and data mapping
Author: Byron Bignell
Date: 01 Mar 2006 12:14 PM

ya, the genrated c# code requires a fair amount of work after the fact. but since I can't write that much working code that fast I'm okay with editing and making adjustments; in an ideal world I'd rather have it just work 100% the first time...but we don;t live in an ideal world...

B

David Karr

Subject: RE: ETL and data mapping
Author: David Karr
Date: 01 Mar 2006 04:52 PM

In my testing, comparing a cached Templates object against the generated
code (after making the thread-safety fixes) to implement the same
translation (both XSLT and Java code generated from MapForce), the XSLT
was almost always faster. If you've done similar testing in the C#
world, I'm guessing that means the C# XSLT engine could be more
performant. I hope you didn't just assume the generated C# code would
be faster.

> -----Original Message-----
> From: stylus-studio-feature-requests Listmanager
> [mailto:stylus-studio-feature-requests.listmanager@stylusstudio.com]
> Sent: Wednesday, March 01, 2006 9:18 AM
> Subject: RE: ETL and data mapping
>
>
> From: "Byron Bignell"
>
> ya, the genrated c# code requires a fair amount of work after
> the fact. but since I can't write that much working code that
> fast I'm okay with editing and making adjustments; in an
> ideal world I'd rather have it just work 100% the first
> time...but we don;t live in an ideal world...
>
> B
>
>
> --
> To reply: mailto:stylus-studio-feature-requests.13964@stylusstudio.com
> To start a new topic:
> mailto:stylus-studio-feature-requests@stylusstudio.com
> To login: http://www.stylusstudio.com/SSDN/
> To (un)subscribe:
> mailto:stylus-studio-feature-requests.list-request@stylusstudio.com
>
>

Byron Bignell

Subject: RE: ETL and data mapping
Author: Byron Bignell
Date: 01 Mar 2006 05:29 PM

Oddly enough I wasn't given the choice. I had to use c#, so perhaps this time I got lucky and didn't have to test as much. Best not trust to luck after this and really hunker down and make sure the resulting code is solid.

If I can get the time in the near future I'd like to see about testing the generated java code against the c# for the work I did. No idea if I'll ever get the time... but one can but hope.

B

Powered by Stylus Studio, the world's leading XML IDE for XML, XSLT, XQuery, XML Schema, DTD, XPath, WSDL, XHTML, SQL/XML, and XML Mapping!

Go to Conference:

Log In Options Username: Password:

Site Map | Privacy Policy | Terms of Use | Trademarks

Stylus Studio® and DataDirect XQuery ™are from DataDirect Technologies, is a registered trademark of Progress Software Corporation, in the U.S. and other countries. © 2004-2016 All Rights Reserved.