running-count issues

[kclark]

Hello!

I am having trouble with creating a running count for each occurance in my fact table.  My fact table contains Orders and Items per Order.  So if an order has 3 items then there will be 3 records with the same order number but different item number.  Because of the issues with distinct counting in Transformer, I am trying to count the Orders distinctly by "Ranking" them (so to speak).  So I create a query item, Order Rank.  Next I use the expression, "running-count([RequestID] for [RequestID])".  Works fine so far.  Problem comes in when I publish as IQD and try to import into Transformer.  I get an error about columns.  Come to find out, the function running-count for Cognos does not transfer to SQL Server.  So I need to change the query expression of my data source to "Database Only" and use a SQLServer - friendly expression for the running-count which I found to be "row_number() OVER (PARTITION BY RequestID ORDER BY RequestID)"....but Framework Manager is not accepting this.  Any help please!!!!! ???

[blom0344]

You are using a database explicit type of SQL with the
row_number() OVER (PARTITION BY RequestID ORDER BY RequestID) part, which would indicate setting the SQL type to native (where it is standard Cognos type)
Running counts are performed on the server, as databases have no real equivalent. Your solution may work if you adjust the SQL generation type of the Query Subject in FM..

[kclark]

Thanks for your reply!  Two quick questions:

1)  How do I change the SQL generation type for the Query Subject (I didn't see it in the properties)
2)  Change it to what?  SQL Native?

Thanks a bunch!

[blom0344]

There is an 'option' link in a SQL query subject allowing you to change Cognos type to  native SQL or Pass-through. Choose Native, not pass-through!!

[kclark]

Thanks.  When I do this at the database layer, I get warning:

Cognos SQL uses a datasource reference qualifier for tables which native RDBMSs do not understand.  You may have to delete the datasource references and add qualifier elements.  Additionally, you may need to chanage the syntax to conform to your RDBMs standard.


Then I get an error when I click ok.  ???

[cognostechie]

When you change it to  'Native' SQL, you cannot use Cognos functions or column names. What is [Request ID] ? That has to be in the form SQL Server would recognise. (Ex: Table Name.Column Name)

Moreover, instead of doing it this way to generate an IQD, you could write the SQL in the database, make sure it works, then cut and paste it into notepad, add the column headers required for an IQD and also the bottom lines to qualify those columns with numbers. Look at any standard IQD generated from FM, take that as a base, paste your custom SQL in between the curly brackets. Whatever is inside the curly brackets can be a non-cognos SQL.

[kclark]

Hmm, interesting.  Ok first, I corrected the error for native settings.  Thank you both.  Unfortunately the row_number()...function is still not being accepted.  Maybe I am writing it wrong???

Looking at your suggestion.  I have in pasted the query in the between the {} do I need...Cognos Query as part of the Header?  Next, then what?  Save it as .txt format?  How do I import that into Transformer?

Thanks for this!

[cognostechie]

Yes, you can leave the Cognos Query as the header. Look at this example:

COGNOS QUERY
STRUCTURE,1,1
DATABASE,ZDW
TITLE,[IQDS].[ORDERS]
BEGIN SQL
{
select
"SALES_ORDER"."SHIP_DT_KEY" "Calendar_Dt",
"SALES_ORDER"."ORDER_AMT" "Order Amount",
"DIM_COMPANY"."CO_CD" "Company_Code"

from

"ZDW"."DBO"."SLS_ORD" "SALES_ORDER",
"ZDW"."DBO"."DIM_CO" "DIM_COMPANY"

where

"SALES_ORDER"."SLS_YR_KEY" >= 2007 and
"SALES_ORDER"."CO_CD" = "DIM_COMPANY"."CO_CD"
}
END SQL
COLUMN,0,Calendar Dt
COLUMN,1,Order Amount
COLUMN,2,Company Code


For this to work, the cognos.ini file needs to have the connection string for database ZDW. After this, you can save it as an .iqd, open Transformer, right-click on the Data Source window and import this .iqd as a Data Source.

The 'Title' on the 4th line can be anything.

That being said, it is best not to put columns from multiple tables in a IQD. It is best to create a seperate IQD for every table and let Transformer associate it with common keys at the lowest level. So in this case, it would be better to create two IQDs, one for Orders Fact and another for Company Dimension and put 'CO_CD' in both of them. That way, Transformer can associate the data from both based on the key.

[cognostechie]

On second thoughts, you can also do this:

Use the Cognos function you started with (running count). Switch the Query subject back to 'Cognos' type. That way, the Query subject would work the way it was working initially.

Now go to Query information tab, you will see two SQLs generated. One is Cognos and the one on the bottom would be 'Native'. You will see that FM re-writes the SQL depending on the what is acceptable to the database. It will re-write the SQL using another function instead of 'running-count'. Take that SQL and put it in the IQD. You just have to format it the way Transformer wants like put alias names in double quotes.

I still don't understand why you would need to do the counting this way. You could do it in the Report using Cognos functions.

[blom0344]

As mentioned earlier cumulative functions have no direct equivalent within a database. If you look closely then you will notice that the Rcount in Cognos SQL is absent from the native SQL in whatever way.

Prior to OLAP style functions one had to code very nasty nested SQL to bring cumulative figures from a database. I think Kclark's solution by using the rownumber() OLAP function is a good solution, but preferably coded on the database itself .

The drawback is that you will not have the flexibility to calculate running counts in any other way then defined in the SQL object. Doing this from the report side allows greater flexibility..

[kclark]

Thanks!

Cognostechie, I am doing the running count to count Orders distinctly in Transformer.  With this measure, I can create a calculated measure in Transformer for if the OrderRank is 1 then 1 else null.  Then sum that calculated measure which is the distinct count of Orders.  I don't think that the Category Count will work as the Order_Number could go beyond the limit...obviously.  Correct?

blom0344, normally I would want to do the running counts in the report side but I need it here for the distinct count of Orders. Unless...there is a better way?

[cognostechie]

The Order Line table that has different Items nos for the order usually has a field called Line No. Don't you have something like that? It should have some field that defines that line as unique for that Order.

If you have Line No, then you could do this in the report (If Line-No = 1 then 1 else 0), The aggregate of that calculated field will give you the number of Orders. Report Studio also has some other functions to determine the first record for a condition.

[kclark]

We probably do but it wasn't include it in the Fact...which I'm sure we can.  But the running-count is doing just what you are suggesting anyway right?

I know I could do that in a report...it's TRANSFORMER that's the issue.  I need to do the distinct count in there (cube).

[cognostechie]

All right..I will give you this solution which I did in a project.

There is a company that is an ISP provider. They wanted to have a Dashboard to show the NUMBER of customers , not the Quantity or Dollar value .. exactly your requirement..right?

Transformer can make three kind of cubes:

1> Regular Rollup Cubes
2> Category Count Cubes (at that time it has an upper limit of 2 million categories, not sure how much now)
3> External Rollup Cube

You can make the 3rd type of cube. If I am understanding it correct, the only thing you need is to count the number of Orders..correct? If so, write a query like 'Select count distinct....', use this query inside an IQD and make the cube. Right click on the measure in Transformer, in the 'Type' tab, select 'External' from the Rollup drop down box.

This would tell Transformer to accept whatever values are provided from the query and Transformer would not roll it up by itself. This disables the rollup in Transformer so you can provide the count from your query.

By the way, in Cognos 8.4, you can use a Package generated from FM instead of using an IQD to build a cube. So if the Query subject was working and the only problem was in generating the IQD, then you can try using the package too with a Regular Rollup Cube.

[kclark]

Geez Louise!  So many options...  Yes, One of my measures is Number of Orders but the others are related to different facts.

Ok, first I will deploy a package from Framework Manager using the default instead of iqd.  Before I do that though I need to create ANOTHER fact table that is distinct count of Orders with attributes that are relative to the Order level.  Correct?

[Lynn]

Regarding alternative to IQD, here is a best practice document:

http://www.ibm.com/developerworks/data/library/cognos/modeling/transformer/page512.html

[cognostechie]

Ok, first I will deploy a package from Framework Manager using the default instead of iqd.  Before I do that though I need to create ANOTHER fact table that is distinct count of Orders with attributes that are relative to the Order level.  Correct?

Yes, but you will have to include the common key column in all of the query subjects that will connect multiple facts and dimensions. It is not recommended to join those query subjects in FM when using in Transformer. The query subjects in the package should be by themselves without any joins and they should have a field that will act as the key to associate them in Transformer. That way the fact data gets linked to dimensions.

[kclark]

Right, I got that...previous lesson on that.  Hehe.  Name the keys the same so that Transformer can identify the joins itself right?

I was a little confused about your process for the externally rollup measure...bare with me please.

I imported the iqd with the Distinct Orders Fact in Transformer.  When you said to right click on the 'measure' did you mean the data source?  And on the 'General' tab select the Contains externally rolled up measure values?  Then I dragged the OrderID of the distinct orders fact to the measures window.  Then from there I right clicked on that measure and on the 'Rollup' tab I selected 'External' from the Regular Rollup drop down.  Is this correct?

From there, I just created a powercube to test my results right?

[cognostechie]

Yup ! You are almost there. In addition to those two settings in the Data Source and the Measure, you will also need to specify that in all the Dimensions. Double-click on the Dimension, In the General Tab, the last one that says 'External Rollup Column', click 'Add', select 'Source' from Association Role, in the Column Name select the column that has the distinct count.

By the way, why would you drop the OrderID in the Measures window? That window should contain only those fields that are the facts, not the IDs. Drop the column there that has the distinct count of orders. 

[kclark]

Sorry I meant OrderCount...I counted the OrderIds (just a misprint). 

Hmm when I check the box "Contains externally rolled up measure values" it removes a dimension out of scope.  Should that be happening?

When you say double click on the Dimension...I have two dimension that the count is assoicated with Time and Orders.  You mean to double click on, say, the Orders Dimension heading in the Dimension Map window, right?  Well...The pop up shows the General tab but there is no external rollup column section...

[cognostechie]

When you say double click on the Dimension...I have two dimension that the count is assoicated with Time and Orders.  You mean to double click on, say, the Orders Dimension heading in the Dimension Map window, right?  Well...The pop up shows the General tab but there is no external rollup column section...

Yes, double-click on the Dimension heading, not any of the levels. First you have to select that 'Contains externally rolled up measure' in the Data Source window on the Fact Data Source. Then do the setting for the Fact in the Measure window. Then it will enable that feature in the Dimensions, otherwise the features won't show there.

Once you do that for one dimension, other Dimensions will be out of scope for that measure. You have to do that for all dimensions.

[kclark]

Ahh yes, I see.  The only dimension other than my Orders is Time...but I used a wizard for that which still shows that the measure is still in scope without me adding it.  Is that ok?

[cognostechie]

Don't know. Build the cube and check the data.

[kclark]

Sorry...how do I do that...just click Create PowerCubes? If so, I get a warning that "A total of 29 records were discarded.  Refer to the log for more details."  What log?  Is this a concern?

[cognostechie]

That error means it did not add data to the cube for 29 records because the association of those records was not proper somewhere. Your data would be less in the cube because of that. There is a log file for the cube which would say more.

You can see where the log file is... From File Menu, select Preferances, then Directories.

Anyway, you can still check the data in the cube, particularly the count. It would not be accurate because of the error but for some levels in the hierarchy, it could be accurate too. Atleast, it will give you the idea whether or not it is working.