none
Fact Count column in ssas

    Question

  • Hello,

    I have a fact table and four dimension tables in my data mart. The fact table contains 8 columns. The Four are the foreign keys for the four dimension tables and when combined form the pk for the fact table. The other four are named: price, yearly change, monthly change and quarterly change. The price column does not contain any null but the other three columns contain null values. When i process the cube, i get 5 variables in the fact measure group : price, yearly change, monthy change, quarterly change and fact price count. The fact price count refers to the price column? If yes whyy is that, maybe becasuse it does not have null values? If yes can i have count column for the other three measures (yearly change, monthly change and quarterly change?) Why are the fact yearly change count, fact monthly change count, fact quarterly change count  are not computed after processing the cube? Can i compute these column with a calcualted member? I thoght i could use a countif function or similar so as to count only the values that are not null. Is that correct? I searched and there was no countif function, if the previous thought is correct how am i going to create the count columns?

    Thanks in advance,

    Andreas  

    • Changed type andrikos Monday, April 30, 2012 10:42 AM Mistake
    Monday, April 30, 2012 10:05 AM

Answers

  • Hi andrikos,

    " The fact price count refers to the price column?" - No sure of that in your project. You can ensure it by clicking the measure and look into the Source property, where specify the count is calculated by column binding or row binding.

    "If yes can i have count column for the other three measures (yearly change, monthly change and quarterly change?) " Please set up the measure with Row biding for the count to see if it is what you are looking for. To do it, find source property in cube designer of Business Intelligence Development Studio, and click on ellipsis button to go to Measure Source window, in the window, you can choose Column binding or Row binding.

    Regards,
    Jerry

    • Marked as answer by andrikos Saturday, May 05, 2012 5:24 PM
    Wednesday, May 02, 2012 3:41 AM

All replies

  • Hi andrikos,

    " The fact price count refers to the price column?" - No sure of that in your project. You can ensure it by clicking the measure and look into the Source property, where specify the count is calculated by column binding or row binding.

    "If yes can i have count column for the other three measures (yearly change, monthly change and quarterly change?) " Please set up the measure with Row biding for the count to see if it is what you are looking for. To do it, find source property in cube designer of Business Intelligence Development Studio, and click on ellipsis button to go to Measure Source window, in the window, you can choose Column binding or Row binding.

    Regards,
    Jerry

    • Marked as answer by andrikos Saturday, May 05, 2012 5:24 PM
    Wednesday, May 02, 2012 3:41 AM
  • Hello,

    I think i found the answer. When i created the cube the proposed meaures where 5:yearly change, monthly change, quarterly change, price and fact price count. I selected all except the fact price count. When the cube was created i went to Cube Structure Tab, expanded the measures, right click in the Price and New Measure. Then i selected Count of Non Empty Values and Selected  “Monthly Change” as the Source column. The measure “Monthly Change Count” was created and it counts the non empty values of the monthly change measure. I did the same for the other three measures and everything worked!

    Thanks.

    Saturday, May 05, 2012 5:23 PM