Hiding objects from lineage

The lineage function is a great tool for users to understand how items are calculated. Business users can use it to fully understand what they’re seeing. The downside of the lineage is that users can fully understand what they’re seeing.

To the best of my knowledge there is no way of telling Cognos to obfuscate a step in the lineage path. This can cause problems if, for example, you use Cognos functions to decrypt a numeric value.

Case in point: an unnamed large company has a field called “Encrypted Data”. The users need to be able to build adhoc queries with the data, but they are not allowed, under any circumstances, to know the decryption formula. A custom database function is out, so it can only be done in Cognos. The users have a tendency to use lineage to double check numbers.

As you can see, lineage leaves a gaping hole in security. Fortunately there is a solution.

The first step is to create a calculation in FM that decrypts the field.

In the properties of the calculation set Is Hidden to true.

Next create a parameter map. Call it Decrypter.
Give it a key. Since the example I’m using is based on the orders table, I gave it the key Order. In the value give it the path of the calculation.

Now for the last step. In the business layer instead of calling the calculation directly you’ll call the parameter map with ‘Order’ as the key.

Now when the user tries to peek in at the lineage he sees this:

Instead of seeing the super secret formula, he only sees the call to the param map. If he doesn’t have access to FM, he will never be able to see the formula now.

Advertisements

9 Responses to Hiding objects from lineage

  1. marc powell says:

    Is it possible to omit a query item in a crosstab from tree prompt which is applied to the query

    I have sales, cancellations and net gains, over a 5 year period, by month, but also need a cumulative total of net gains to show closing customers

    I have a period tree prompt but if i select a period the running total starts with first date selected,

    I need net gains to start with the selection, but cumnet gains to start from day 1 and end with the tree prompt selection

    Thanks Marc

  2. cognospaul says:

    Hi Marc,

    What you’re asking for should be possible, depending on your query and how your crosstab is set up.

    I’m imagining your crosstab has Months on the rows, and the measures in the columns.

    Is this a purely dimensional source, or is it DMR? Are you using detail filters and the running-total function, or are you using something along the lines of total(currentMeasure within set periodsToDate(currentMember(),[Level]]))?

    • marc powell says:

      Hi

      Purely dimensional

      Months as Columns
      P&L items as Rows eg Sales, Cancellations Net Gains
      Default measure in the crosstab is QTY
      Then have a running total on QTY
      and a Tuple on running total QTY and Net Gains to give me cumulative customers

      Cheers Marc

  3. cognospaul says:

    You’re using the running total function?

    • marc powell says:

      Yes
      Sorry if not clear
      Then have a running total on QTY
      and a Tuple on running total QTY and Net Gains to give me cumulative customers

      • cognospaul says:

        To begin, running totals may not work as expected on a multi-dimensional source. I strongly recommend switching to the method I detailed previously here.

        With this method you could do something like:
        total([cube].[Measures].[NetGain] within set periodsToDate([AllLevel],currentMember([TimeHier])))
        For each month it will return the total from the beginning of the time dim to that month. You can then subtract the previous years by adding – total([NetGain] within set except([YearLevel],[5YearSet]))
        total([cube].[Measures].[NetGain] within set periodsToDate([AllLevel],currentMember([TimeHier]))) – total([NetGain] within set except([YearLevel],[5YearSet]))

        But you also said that you want the running total to only go up to the date selected. Are the users likely to select individual days, or just up to the month? If it’s just to the month you might be able to do something like:
        total([cube].[Measures].[NetGain] within set intersect(periodsToDate([AllLevel],currentMember([TimeHier])),periodsToDate([AllLevel],[SelectedMonth]))) – total([NetGain] within set except([YearLevel],[5YearSet]))

        The intersect will ensure that only months before the selected date will be used for the total.

  4. John Mills says:

    I tried this, it works fine in FM but in QS after publishing the package I get a message about a 2 part reference error which is a a depricated feature. We are running Cognos 8.4.

  5. John Mills says:

    I got this working ok, of course you must tick the calculation for inclusion in the published package.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: