2 Replies Latest reply on Jul 19, 2016 1:20 PM by Shane Conrow

    Count dimension Cube data source

    Julian Schönfelder

      Hi!

       

      I am connected to a OLAP Cube (MS Analysis Services) with Tableau and want to be able to use the count function.

       

      I have a list of proposals/deals and the only thing I want to do is to count the total number of proposals. I cannot find a way of doing it…. But I see the total count when I use the summary legend.

       

      I also tried to create a calculated field with: "sales value <> 0 then 1 else 0" then I thought I would be able to sum it but the calculated field appeard as dimension (blue) and no calculations were possbile to perform.

       

      Please advise!

        • 1. Re: Count dimension Cube data source
          Carl Slifer

          Hi Julian,

           

          I do not have a cube data source to test a few things with but check out this: It's Tableau's known list of difference when using a cube Understanding Functional Differences Between OLAP and Relational Data Source Connections | Tableau Software .

           

          I am not sure how much help that can be for you, but it does allow table calculations if you're able to experiment with those. Cubes are not the most efficient way of playing in Tableau. Everything about them is fairly anti-tableau as the root concept is to not query the data source and to already have every possible calculation made as a static value or to wait three weeks while asking your DBA for this information to be updated. While Tableau is designed to work on a relationship database or flat file and this allows the end user to play and ask the type of questions you are asking. For now, look into using WINDOW functions such as WINDOW_SUM. Sorry that its not much help in this case.

           

          I've seen users use a download of the cube and drop this in excel or access so that they had this ability or even use an ETL tool so that they could take a snapshot of the Cube and them make this into a .TDE file so that tableau could play with it.

           

          Cheers

          Carl Slifer

          InterWorks

          • 2. Re: Count dimension Cube data source
            Shane Conrow

            I'm working through the same issue. I've found a "solution" but it has its flaws. Here is what I have done. (tableau 9.0 if it matters)

             

            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.

             

            So what DOESNT this do? It doesnt give me a mark with the total number on it, it only gives me the bar. Each individual user is a row to create the bar, so each one has a value of 1. So far I have been unable to find a way to get that total mark to appear anywhere (I'll be posting a separate question asking about how to do that.)