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:
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
select <stuff and things>
from SRLocationReference fact
where fact.SnapshotDateID = 20231007it'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:
[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:
QuoteXQE-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.