In this post, we are going to discuss some helpful tips for using the Data Extractor in SimCorp Dimension. Whether you are new to SimCorp Dimension or have multiple years of experience, we hope these insights will be of value to you and your organization further down the line.
Using the SimCorp Dimension Data Extractor can be a challenge for any experienced SQL developer, the advanced data extractor might be the ideal tool if that is available in your installation. But for those of us who are not experienced SQL developers, the visualization of the tool can be quite powerful. We would even go as far as to say that a business user with no SQL knowledge can use it to build their own reports with just a few hours of hands-on training.
As long as you understand the basics of joining tables and a few additional tips, you should be able to create simple extracts to get you started. In our opinion best way to find table names and fields is using the help text by right-clicking on the target field and select technical details. A simpler way with 1 less click is to again right-click on the field and select Audit trail, this will then show the table and field name separated by a dot (.). Note you will have to have an exemplary record loaded in the window in order to access the audit trail view.
Once you have found your fields and you have selected tables in the extract, you can enter them by writing the full name or use a wild card to limit the number of options. For example, writing ‘audit%’ and then pressing F4 to give you a far shorter list than a full F4 on the field. You can also toggle between SQL name or name of the table using the options menu for easier searching.
Hidden in plain sight is a field called ‘SQL expression’. Here you can enter your own SQL code that can either be used as output or for other purposes. Activate this in a blank line by right-clicking and select edit. An editor will open where you can do anything from simple transformation from string to integer over to more complex lookups, what-ifs, etc. This can be very useful if you want to join a text string with an IK value, for example from the audit tables or segments.
You might have noticed that when working with variables, sometimes certain fields do not allow any value selection in the pop-up when you preview the data. It can often be solved if you select the foreign key rather than the primary key. For example, if you want to show the currency from the securities table, don’t select the currency table, instead select the securities table and then the currency field. This different selection strangely enough gives you a pop-up selection.
The extra tab is useful for any additional conditions or joins. A simple developer trick is that you can set a condition on an internal key IK in the extra tab, which the conditions tab does not allow you to do; something that can be useful when building and testing your extract making sure to get the right data.
One last hint of advice I will leave you with is to test the extract as you go along. Especially if you add inline view, work with aggregation functions, or join to grid views. You want to make sure you have the exact number of records in your example and not twice as many or quadruple. A simple join can easily explode the number of records returned that you did not wish for.
Hopefully, we have enlightened you to some time-saving and optimizing techniques for extractions. If there are any further questions or comments you may have in regards to this topic don’t hesitate to let us know.