7 Replies Latest reply on Jan 6, 2012 9:52 AM by Ed Egan

    Microsoft Analysis Services Question

    Ian Waring

      This is why I hate cubes. Try and filter a list of less than 4,000 items to select 4 specific ones for a query, 2 minutes, then boom:

       

      Analysis Services database error 0x80004005: Error during memory operation. Error Code = 0xC1030001, External Code = 0x00000008: Not enough storage is available to process this command. .

      Analysis Services database error 0x80004005: Error during file operation. Error Code = 0xC1020003, External Code = 0x00000000:.

       

      Gobs of space on my hard disk, 4GB memory with only Tableau running, so... is this the server being sick on my shoes? Or does anyone recommend playing with Virtual Memory settings locally?? (i'm not sure where the core issue is!).

       

      Any help or guidance would be most welcome.

        • 1. Re: Microsoft Analysis Services Question
          Ed Egan

          Hi Ian.  4 GB on your VM is fine.  Are the 4000 items already pulled into a graph or chart within Tableau or does your FACT table only contain 4000 items?  I'm just trying to understand what you started with prior to the error.  Can you duplicate your data pull browsing the cube in SSMS?  Do you have access to Adventure Works where you can try to duplicate the error while browsing the AW cube with Tableau.  One more thing, as I said I have no idea what steps your taking but make sure your data pull starts with a measure then add dimensions.  You may or may not know this, but pulling multiple attributes from multiple dimensions prior to pulling a FACT may cause memory issues as well.   

          • 2. Re: Microsoft Analysis Services Question
            Ian Waring

            Hiya,

             

            I think it's a cube stability issue. I started with a view of a few hundred transactions (order number, customer name, invoice value, cost, margins) on one vendor i'd filtered (Cisco in this case). Then realised they'd taken over Ironport, Linksys and Tandberg, so wanted those added to the filter. There are some 4,000 different vendors on our list of vendors, and as soon as I tried to open up the filter, got the errors as highlighted.

             

            So, what I did was to make the vendor a global filter, open up a blank worksheet, filter down to the 4 vendors I wanted, and then just clicked back to the transactions worksheet. That worked.

             

            So it looks like the cube is temperamental if it's carrying a view down the cube hierarchy and I try to change the filter while it holds onto the view it's got. As soon as I make life slightly simpler for it, it works.

             

            I still hate cubes. Almost like a cynical computer scientist doing whatever's needed to save the computer work, and putting constraints on me doing my own job productively by doing so. But that's a rant for another day...

            • 3. Re: Microsoft Analysis Services Question
              Ed Egan

              Hey Ian...well said. Cubes work in my world but having gone through the growing pains of building an OLAP solution, I can appreciate your take and sense of humor about it all. Glad you got it working.

              • 4. Re: Microsoft Analysis Services Question
                Ian Waring

                So... just need to do a calculated field called "SLA Exception" to interpret a text field that sometimes contains one of 11 reasons why we can't get a quote processed in time - I need to say SLA met, SLA missed, or each exception type, what outside our direct control is holding things up. Create Calculated Field - oh ****, you can only do that based on Measures, not on Dimension fields. The design philosophy of making things easier for computers, while putting poor users in operational strait jackets went out in the 1960's I thought. I still hate cubes!!!

                • 5. Re: Microsoft Analysis Services Question
                  Ed Egan

                  Funny!  I get your point though...Tableau will only do calculations against measures when hitting an OLAP source.  What about creating sets?  So you have an dimension attribute that will contain 1 of 11 values, right?  Broken out, several reasons may be interpreted as "SLA Missed" others may be interpreted as "SLA Met", etc..., correct?  You could create one set for "SLA Met" and another for "SLA Missed", granted you would have to do two separate views in Tableau, one for "missed" and one for "met" but you can use the two views in one dashboard and filter each by Vendor.  I know, not ideal but works.  The best solution in this scenario, being it's something you'll use over and over, is to create a SQL view in your relational and then pull it into your OLAP solution.  The SQL view will need to have a join to the FACT table as to pull in the FACT primary keys in order to join in your OLAP solution.  It works but can be done and yet again...I feel your pain.  Once you have those type of scenarios figured out however the OLAP solution works well in Tableau.     

                  • 6. Re: Microsoft Analysis Services Question
                    Ian Waring

                    I have a more elegant solution. I pick the fields I want to work with, generate a view of the data I need, move it as a crosstab into Excel, save it, open a new instance of Tableau, open the Excel spreadsheet, create an extract and do whatever I want to with the data without all the silly restrictions. Very liberating!

                     

                    Have a nice weekend. Now off to chill :-)

                    • 7. Re: Microsoft Analysis Services Question
                      Ed Egan

                      And there's that...!  Have a good one Ian.