queryValue()

[dougp]

Using Cognos Analytics 11.2.4.2

I'm trying to take advantage of database partitioning to improve query speed.  If I filter the partition key to a deterministic value, I can get significant performance improvement.

My fact table (SRLocationReference) is partitioned on SnapshotDateID.

If I filter like this:

Code Select Expand

select <stuff and things>
from SRLocationReference fact
  inner join SnapshotDate sd on sd.SnapshotDateID = fact.SnapshotDateID
where sd.Status = 'Current'

My query is slow.  But if I filter like

Code Select Expand

select <stuff and things>
from SRLocationReference fact
where fact.SnapshotDateID = 20231007

it's fast.

My model structure looks like

Physical
    RoadwayDM
        SRLocationReference
            SnapshotDateID
            <other query columns>
        SnapshotDate
            SnapshotDateID
            Status
            <other query columns>
        <other tables>
Business
    Roadway
        SR Location Reference
            <query items>
        Snapshot Date
            <query items>
        <other query subjects>

Knowing that macros perform calculations then pump the results to the query string as hard-coded values before sending it to the database server, I figured I could create a filter on the SR Location Reference query subject (Business > Roadway > SR Location Reference) like this:

Code Select Expand

[RoadwayDM].[SRLocationReference].[SnapshotDateID] = #queryValue('[RoadwayDM].[Snapshotdate].[SnapshotDateID]', '[RoadwayDM].[Snapshotdate].[Status] = ''Current''')#

All of the references were drug from the Model tab in the Available Components pane in the Filter Definition dialog.  So, no misspellings.

Framework Manager is complaining:

XQE-V5-0005 Identifier not found '[RoadwayDM].[Snapshotdate].[SnapshotDateID]'.

I verified my quotes are good.  The query items exist.  My syntax looks just like the example shown when I click on queryValue() in the parameters tab.  What's wrong here?



and...
queryValue() has become very useful for me in Reporting.  I was hoping to use it in FM
Yes, the model is using DQM.  If it was CQM, I would expect queryValue() doesn't appear in the list of macro functions on the parameters tab.