5 ms·

I am currently working as an Actuarial Analyst, but I have also worked several years as a programmer. As an analyst the tools I use are excel, access, SAS Ente

I am currently working as an Actuarial Analyst, but I have also worked several years as a programmer. As an analyst the tools I use are excel, access, SAS Enterprise guide and Oracle SQL developer. One of the big problems I face is that we have no good way to abstract away a process and really make it reusable. My general work flow is using SAS to pull data from multiple sources, combine and run the data through some series of logic/calculations. Then take the resulting data, copy to excel for some additional analysis or report. This might be for a monthly/quarterly report or an analysis that needs to be update with the additional runout of data. But these steps are all tightly coupled together. If I want to rerun the same logic on a different data set, or an updated data set I will copy and paste all of the files, update the queries. I have no way to bundle them together so that I can easily reuse with different data sources, or refreshed data. Really want I want is someway to encapsulate different sets of data transformations/calculations into to functions to reuse them in different contexts and among different people.

myoffe 11y ago
I haven't used it extensively, but SQL Server Integration Services (SSIS) looks like it does a lot of the things you're talking about.
- wesd 11y ago
  It does. There are other ETL tools as well. https://en.wikipedia.org/wiki/Extract,_transform,_load#Tools https://en.wikipedia.org/wiki/Extract,_transform,_load#Tools
jsandiego 11y ago
We are having a similar set of issues where I work (insurance industry). Always looking for folks to chat with/discuss similar issues
- drdoom 11y ago
  Is there a way to get in touch with you?
  - jsandiego 11y ago
    Sure email is ios at arrowheadgrp.com
dgudkov 11y ago
Look at my EasyMorph (http://easymorph.com http://easymorph.com). It's a visual replacement for scripted data transformations. People use it to replace SAS and Visual Basic scripting. It also allows creating reusable modules. Contact me at <hnusername>@easymorph.com if it looks interesting to you.
- shaunxcode 11y ago
  Hey, I clicked through, read the tutorial, got excited about your examples.. tried to download and found out it was windows only! I would have totally evaluated it further if there were an os x/linux option.
  - dgudkov 11y ago
    Thanks for checking it out! As we're targeting Tableau users so eventually we will release an OS X version.
    - nycdatasci 11y ago
      Speaking of Tableau (which was founded on the concept of VizQL), how is this different? Doesn't tableau basically enable knowledge workers to create data-centric web applications?
  - hbt 11y ago
    use a virtual machine or install windows as dual boot. If this software solves a problem you are really having, nothing would stop you.
    - CognitiveLens 11y ago
      if solving one problem involves creating another, it's good to be cautious.
- mistermann 11y ago
  Does this have an API that can be called from .Net? I'm really liking some of the things Microsoft is doing with Power Query, but I don't like how it is (afaik) only callable from Excel or PowerBI online. I'd like similar capability, but more open, and could be called via scripting, from SQLCLR, etc. Another big hitch: Microsoft has not published proper API's for manipulating PowerPivot models in Excel, and I don't think they intend to - I've heard one 3rd party has reverse engineered the API's (you can decompile the .Net binaries, but I haven't had the time to look at it yet).
icebraining 11y ago
We use Pentaho Kettle for those kinds of transformations. It's FOSS, and connects to a whole bunch of programs and formats. It's a graphical tool - you drag-n-drop modules, then configure and connect them, though it can also run scripts (it has JavaScript, Java, Bash and Ruby support, besides SQL, of course) - but after configuring the transformation/job, you can also run it on the terminal, which is useful for periodically re-running it. http://community.pentaho.com/projects/data-integration/ http://community.pentaho.com/projects/data-integration/
- mindcrime 11y ago
  I've been doing a lot of work with Kettle as well, and it is a handy tool (albeit with a few warts). What I think would be handy for use in an organizational setting, where "business users" might want to use some of the transforms, would be a way to publish transforms somewhere, making them discoverable and accessible to others. I don't want to make it sound like I'm talking about UDDI or anything (although, thinking about it, maybe you could use that), but just an easy way for a Joe Business User to get a list of available transforms, some explanation of what they do, what input they take, what they output, etc. And maybe a way to make changes to the "small stuff" (like the input and output path, for example) without having to load up Spoon and edit the ktr that way. Since transforms can be parameterized, that should be doable... You could also picture combining this with something like a Yahoo Pipes like web interface, to let you define your own chains of transforms and operations as well. And hell, a web-based interface for editing ktr files would be a pretty interesting thing as well, if somebody would build it.
  - mhw 11y ago
    Have a look at Alteryx (http://www.alteryx.com/ http://www.alteryx.com/) - it's pretty close to what you're describing, I think.
mcarroll_ 11y ago
@rgoddard - May be a bit overkill but check out Immuta (www.immuta.com). Its a data platform, built for data scientists, that enables you to query across many disparate sets of data using familiar patterns such as SQL, file system, etc. Our SQL interface allows you to hook to Excel, Tableau, Pentaho...so you could write your abstracted logic and connect to many data sources or mashed up analytic results. contact me at matt@immuta.com if you're interested after reading through the site.
knn 11y ago
The databricks platform should solve exactly your problem - reusable data pipelining/transformation. I saw a demo of it last night and it was extremely slick. Their product is amazing, it makes data pipelining incredibly easy compared to setting up a hadoop cluster and running hive/etc. (I don't work for them - but if any databricks employee sees this, please hire me!) It runs on a spark cluster over AWS, which is much more modern and powerful than SAS/excel/sql. Since you know how to program already, it shouldn't be too hard to pick up spark (even has python bindings)

myoffe 11y ago

I haven't used it extensively, but SQL Server Integration Services (SSIS) looks like it does a lot of the things you're talking about.

wesd 11y ago
It does. There are other ETL tools as well. https://en.wikipedia.org/wiki/Extract,_transform,_load#Tools https://en.wikipedia.org/wiki/Extract,_transform,_load#Tools

jsandiego 11y ago

We are having a similar set of issues where I work (insurance industry). Always looking for folks to chat with/discuss similar issues

drdoom 11y ago
Is there a way to get in touch with you?
- jsandiego 11y ago
  Sure email is ios at arrowheadgrp.com

dgudkov 11y ago

Look at my EasyMorph (http://easymorph.com http://easymorph.com). It's a visual replacement for scripted data transformations. People use it to replace SAS and Visual Basic scripting. It also allows creating reusable modules. Contact me at <hnusername>@easymorph.com if it looks interesting to you.

shaunxcode 11y ago
Hey, I clicked through, read the tutorial, got excited about your examples.. tried to download and found out it was windows only! I would have totally evaluated it further if there were an os x/linux option.
- dgudkov 11y ago
  Thanks for checking it out! As we're targeting Tableau users so eventually we will release an OS X version.
  - nycdatasci 11y ago
    Speaking of Tableau (which was founded on the concept of VizQL), how is this different? Doesn't tableau basically enable knowledge workers to create data-centric web applications?
- hbt 11y ago
  use a virtual machine or install windows as dual boot. If this software solves a problem you are really having, nothing would stop you.
  - CognitiveLens 11y ago
    if solving one problem involves creating another, it's good to be cautious.
mistermann 11y ago
Does this have an API that can be called from .Net? I'm really liking some of the things Microsoft is doing with Power Query, but I don't like how it is (afaik) only callable from Excel or PowerBI online. I'd like similar capability, but more open, and could be called via scripting, from SQLCLR, etc. Another big hitch: Microsoft has not published proper API's for manipulating PowerPivot models in Excel, and I don't think they intend to - I've heard one 3rd party has reverse engineered the API's (you can decompile the .Net binaries, but I haven't had the time to look at it yet).

icebraining 11y ago

We use Pentaho Kettle for those kinds of transformations. It's FOSS, and connects to a whole bunch of programs and formats. It's a graphical tool - you drag-n-drop modules, then configure and connect them, though it can also run scripts (it has JavaScript, Java, Bash and Ruby support, besides SQL, of course) - but after configuring the transformation/job, you can also run it on the terminal, which is useful for periodically re-running it. http://community.pentaho.com/projects/data-integration/ http://community.pentaho.com/projects/data-integration/

mindcrime 11y ago
I've been doing a lot of work with Kettle as well, and it is a handy tool (albeit with a few warts). What I think would be handy for use in an organizational setting, where "business users" might want to use some of the transforms, would be a way to publish transforms somewhere, making them discoverable and accessible to others. I don't want to make it sound like I'm talking about UDDI or anything (although, thinking about it, maybe you could use that), but just an easy way for a Joe Business User to get a list of available transforms, some explanation of what they do, what input they take, what they output, etc. And maybe a way to make changes to the "small stuff" (like the input and output path, for example) without having to load up Spoon and edit the ktr that way. Since transforms can be parameterized, that should be doable... You could also picture combining this with something like a Yahoo Pipes like web interface, to let you define your own chains of transforms and operations as well. And hell, a web-based interface for editing ktr files would be a pretty interesting thing as well, if somebody would build it.
- mhw 11y ago
  Have a look at Alteryx (http://www.alteryx.com/ http://www.alteryx.com/) - it's pretty close to what you're describing, I think.

mcarroll_ 11y ago

@rgoddard - May be a bit overkill but check out Immuta (www.immuta.com). Its a data platform, built for data scientists, that enables you to query across many disparate sets of data using familiar patterns such as SQL, file system, etc. Our SQL interface allows you to hook to Excel, Tableau, Pentaho...so you could write your abstracted logic and connect to many data sources or mashed up analytic results. contact me at matt@immuta.com if you're interested after reading through the site.

knn 11y ago

The databricks platform should solve exactly your problem - reusable data pipelining/transformation. I saw a demo of it last night and it was extremely slick. Their product is amazing, it makes data pipelining incredibly easy compared to setting up a hadoop cluster and running hive/etc. (I don't work for them - but if any databricks employee sees this, please hire me!) It runs on a spark cluster over AWS, which is much more modern and powerful than SAS/excel/sql. Since you know how to program already, it shouldn't be too hard to pick up spark (even has python bindings)