Happy Pi Day!

Today is March 14 ... ie 14th day of the third month .. 3.14!  If you really want to get technical, we should've celebrated at 1:59 this morning which would have made it 3.14159!  Whatever!  Have some pie to celebrate Pi!

Removing Tracked Changes in MS Word.

Have you been working on an MS Word document and had the Track Changes turned on?  Did you ever try to remove the tracking so that when you send the document to someone else they don't see the tracking?  Most often people select the Final option in the Tracking menu thinking that it would go away even after saving the document.  The problem is the tracking still shows.  The proper way to turn off all the tracked changes is to Accept all the changes. The Accept feature is located in the Review ribbon under Changes.  Now when you save the document, the different colors and strike-throughs won't show after you save the file.  Thanks Pamela for this suggestion.

 

Informatica Debugger

Debugging with Informatica's ETL can be confusing. It does take a bit of practice and could present with a big learning curve if you're just starting out.

Thanks Christine for putting this together.

Informatica Debugger Tips & Tricks

Debugger Toolbar Menu:

Starting the debugger:

Clicking on Start Debugger from the toolbar will bring up the Debug Mapping wizard. Always select DWH2IntegrationServ from the drop down list, and “Use an existing session” when you want to debug a mapping.

 
 
 
 
 
 
 
Navigating in the debugger:


Once the debugger session is launched, there are many ways to navigate to the data that you want to see:
i) Next instance button: Walks through each transformation in the order that Informatica processes them.

ii) Step to Instance button: click on the transformation that you want to view and then click this button. The debugger will skip all transformations in between. Note that you can select any previous transformation from the drop down list in the Debugger Data Display window if you want to check what the data was at an earlier step in the mapping for the current row of data.

 
 
 

 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
iii) Using breakpoints


Breakpoints allow you to set criteria to determine when you want the debugger to stop and display data

- click on the transformation where you want the debugger to stop, and then on the Edit Breakpoints button on the toolbar

- click Add in the Edit Breakpoints window – notice that a breakpoint is displayed in the bottom window. If you click OK at this point, the debugger will stop by default on the selected transformation for each row of data in the mapping.

If you want to stop at a particular row of data, add a Condition by clicking on the Add button above the condition window:

 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
Define your condition, select OK, and then use the green arrow Continue button in the debugger toolbar. You can add multiple conditions.


NOTE: if you really just want to look at one row of data and you’re selecting it from a large source table, it is more efficient to put a filter on the session in thecorresponding workflow rather than setting a breakpoint



Checking the data

i) Eyeballing:

Often it is enough to check the values as they show in the Debugger Data Display window for the different transformations to identify what the problem is in a mapping



ii) Evaluating expressions:

If eyeballing isn’t enough, you can right-click in the top-left corner of a transformation and select Evaluate Expression:

Enter the expression you want to evaluate in the Formula window, and then select Evaluate:

Informatica will validate the expression and then display the result:

Tips for evaluating expressions:


- You can open the transformation by double-clicking on it in the mapping window, copy an expression from your mapping, and then paste it into the Evaluate Expression formula window, rather than recreating it



- You can test whether an expression is true or false. For example, you could test if the expression above generates a date that is later than 1996 by changing the formula to:

TO_DATE(ZZRCRTDATE, 'YYYYMMDD') > TO_DATE('19970101', 'YYYYMMDD')

Value will be 0 if the expression is false, or 1 if it’s true





- The evaluate expression option is available in Expression, Filter, Rank, Router and Update strategy transformations



- You can use Evaluate Expressions to display mapping variables or parameter variables in



Viewing target data

The target data is displayed in the Debugger Target Data Display window. The ROW-TYPE column tells you if a row is Inserted, Deleted, or Filtered from the target table.



Stopping the debugger

To shut down the debugger when it has paused on a transformation, click on the Stop the Debugger toobar button.



To stop the debugger when it is processing data, click on the Break Now button to pause the debugger and then you will be able to stop it.

Debugger “Gotchas”

- sometimes the debugger stops working and an error comes up saying that it failed to initialize. We have not found a work-around for this, other than restarting the Informatica service on the server.

- In our experience, it is very difficult to debug mappings that use joiners. Even with breakpoints the debugger seems to skip over transformations that come downstream from the join.

Outer Join constructs

If you're ever looking for help understanding Outer Joins and coding practices, check out Terry Purcell on one of IBM's sites. The notes are in two parts.

A comparison of simple outer join constructs

Advanced outer join constructs


(Thanks Diana for the URL)

Persistent Cache in Informatica

Doing a lookup in Informatica with large tables is time consuming especially if the same table is being used often. Multiple Lookup transformations run sequentially. To help improve performance and minimize hits on the table, setting up persistent caches that run concurrently is recommended. Details are available on the Informatica Knowledge Base site (you'll need an account to get in).

See https://communities.informatica.com/infakb/whitepapers/1/Pages/15123.aspx

(Thanks Louise)

PowerPlay Drill-Through - Using Measures

Cognos PowerPlay Web cubes can drill-through to Web Reports created in Report Studio. One of the challenges encountered is creating drill-throughs when a cube contains several measures. If you select a particular measure and want to drill-through to a report containing detailed records, you might get more records than you needed. The key here is creating several reports that are identical with the exception being the filter used for the measure that's of interest.

First, let's go over how to create/define a drill-through from PowerPlay.

1. In Transformer, go to the Measures box. For each measure that needs to have a drill-through, select that measure and then go to the Drill Through tab.
2. Add a file name for that measure . . example C:\Headcount.crr. Click OK.
3. Go to the next measure (if you need to) and do the same things. That is, go to the Drill Through tab and add another file name.
   
4. Save the Transform file.
   
5. Build a cube as you normally do.
6. Copy the cube to he Web Server.
7. In Report Studio on the web, create a report with the same name as the one created in the Drill Through tab for a particular measure. So if you created a file called Headcount.crr then create a web report called Headcount. Create an appropriate filter based on the measure . . . eg if there is a measure called head_count then set head_count = 1.
8. Save the web report and make a copy of it.
9. For each other report, change the filter required for that particular measure. The idea is the reports should be identical with the exception being the measure that is being filtered.

When you go to the PowerPlay cube and decide to drill-through, it will select the appropriate web report based on the report name and filter that has been assigned.

Of course, the more drill-throughs you come up with means you have to create a duplicate one for every measure. If you have 3 measures, then you will need to create 3 x the number of reports.

(Thanks for finding this Christine)

Date Difference

What day will it be 121 days from day? I don't know either but there's a website that can help find that out. Type in the date and the number of days and it will tell you. The URL is http://www.datedifference.com/ . . of course, what else would be?