9 Replies Latest reply on Aug 29, 2011 5:49 PM by Austin Dahl

    How to group in Microsoft Analysis Services?

    Rogerio Costa

      Could someone please help me to find out How to group in Microsoft Analysis Services?

       

      The group bottom is disable!

       

      Thanks a lot!

        • 1. Re: How to group in Microsoft Analysis Services?
          . budysugi

          When connecting to OLAP, grouping member feature is not functional (as well as member alias). You need to create group using MDX expression.

          • 2. Re: How to group in Microsoft Analysis Services?
            Austin Dahl

            You could consider doing data blending.

             

            1.  Create cross tab view with your dimension and any old measure.

            2.  Export the cross tab to Excel.

            3.  In Excel, rename the measure column to a group name.

            4.  Edit the spreadsheet cells and pick a group for each member.

            5.  Save the spreadsheet.

            6.  Back in Tableau, connect to the spreadsheet.

            7.  Use data blending to view your cube measures by the group you created in the spreadsheet.

             

            Austin

            • 3. Re: How to group in Microsoft Analysis Services?
              Ed Egan

              I started following Dahl's suggestions, step-by-step, but keep running into an issue.  When I get to step 7, basically bring in my group as a quick filter to my view, I always get the character "*" (the multiplication character) and "Null" without fail.  If I open the excel sheet in a separate view on its own (with no relationship to my cube), then it's clean, I don't get the * or Null values.  Any help would be appreciated.

              • 4. Re: How to group in Microsoft Analysis Services?
                Austin Dahl

                Sorry EJ, there's a subtlety that my explanation is missing, which is that you want the cube to be the primary data source and you want to have your  cube dimension somewhere in the view (row, column, level of detail, etc).  Here's some more explicit steps.

                 

                7. a. Create a new worksheet.

                7. b. While it is empty, select the original cube data source.

                7. c. Drag out the cube dimension onto rows (or columns or level of detail, etc.), making the cube the primary data source.

                7. d. Staying on the same sheet, select the spreadsheet as the data source

                7. e. Drag the group name field onto rows (making the spread sheet a secondary data source).

                 

                I hope that works out better.

                • 5. Re: How to group in Microsoft Analysis Services?
                  Ed Egan

                  Austin...Thank you for your prompt reply and for the added detail.  I'll work with this.  It's a bit tricky as the relationship from my cube to the excel data is joined by multiple columns.  If I remove a certain attribute from the view that is also part of the relationship, it does not work.  However I can remove another attribute from the view that is also tied to the excel data, and there is no impact.  Interesting.  I'll try to simply the data in my excel sheet and see if that works better for me.  Thanks again Austin.

                  • 6. Re: How to group in Microsoft Analysis Services?
                    Ed Egan

                    Austin...Thank you for your prompt reply and for the added detail.  I'll work with this.  It's a bit tricky as the relationship from my cube to the excel data is joined by multiple columns.  If I remove a certain attribute from the view that is also part of the relationship, it does not work.  However I can remove another attribute from the view that is also tied to the excel data, and there is no impact.  Interesting.  I'll try to simplify the data in my excel sheet and see if that works for me.  Thanks again Austin.

                    • 7. Re: How to group in Microsoft Analysis Services?
                      Austin Dahl

                      Yeah, multiple columns tends to make it more complex.  In general, you need to include all the cube dimensions that are replicated in the spreadsheet.  If you drop just one, then it cannot blend them.

                       

                      Starting with something simple, like a single dimension, will help you get a feel for it, so you have a good plan.

                      • 8. Re: How to group in Microsoft Analysis Services?
                        Ed Egan

                        Can you have more values in your excel source than exist in the cube?  Let me clarify.  If you create a list within Tableau just by dragging a dimension attribute to your row shelf, you get all attribute values (even if they don't tie to your fact table).  However I use an attribute as well as a measure, then it eliminates a number of rows (eliminate values that have no data within fact table or measure group).  Those attribute values that do not have any related data in my fact table are now eliminated.  Or does the excel spread sheet always have a one-to-one relationship with the dimension attribute values that only tie to the fact table?  Hope this makes sense. 

                        • 9. Re: How to group in Microsoft Analysis Services?
                          Austin Dahl

                          Yes, you can have one-to-many relationships from Cube to Excel file (and even vise versa, which I guess would technically be many-to-many).  When you do that that the measures may get aggregated.  It's a bit like doing a left join after aggregating.