1 Reply Latest reply on Aug 24, 2016 12:11 PM by diego.medrano

    Creating totals of dimension data using an SSAS Cube

    Shane Conrow

      I am currently on 9.0 (no control there, thats what they have our server on)

      My data source is an SSAS Cube, which plays a distinct role in my problem here.

       

      What I am trying to create is a few charts that measure how many distinct users have accessed a part of the system each month. I have the data for when each user accesses the system in the cube. However, being a cube I cannot simply count them as I would in a sql database. So far I have found a way to create some charts (currently using a bar and a pie) that will give me size based on the number of distinct users. The problem is, each slice is the actual user and so it has an individual count of 1. I need to add to this, either as a mark or in a tooltip, the total of all of the users that make up that bar.

       

      Here is what I have done so far. I am completely open to doing it another way if it gets me what I need, as long as it works with my data source.

       

      1. Create a calculated field (called mine Distinct Count) and then in the formula simply put

      Size()

      2. Add the dimension you want to count to the marks

      3. Add the calculated field to the Rows (at least for a bar chart)

      4. Open the menu for the calculated field in the row and calculate by cell.

      5. Drag your grouping (for me, month) into the Columns

      I now have a bar that gives me a count of the number of distinct users who have accessed the system each month.