Dynamic set query

Question

Hello All,

I've small question:

when I create this in mdx script, I can see the set folder in the dimension,

 

CREATE DYNAMIC SET CURRENTCUBE.[HighLatency]
 AS FILTER(
 {([Dim].[Hierarchy].[level].MEMBERS)},
[Measures].[latency]>=800),
DISPLAY_FOLDER = 'Set'
  ;

when I create using crossjoin, i cannot see the set folder in either dimension.

 CREATE DYNAMIC SET CURRENTCUBE.[HighLatency]
 AS FILTER(
 CROSSJOIN(
 {([Dim1].[Hierarchy].[level].MEMBERS)},
[Dim2].[Hierarchy].[level]),[Measures].[latency]>=800),
DISPLAY_FOLDER = 'Set'

Can somebody please help me understand? Does that mean i cannot refer multiple dimensions or do I need to explicitly define the set location?If so how?

Much appreciated!

Rok

Answer 1

I think the issue is that when you create a set from members in a single dimension, Analysis Services knows where to define the display folder. However, when multiple dimensions are involved it can no longer determine the correct place for the set's display folder.

I presume that the display folder 'Set' does exist but just not in either of those dimensions yes?
Update: Just managed to test this and I see that it doesn't display anywhere (in SSMS and Excel2003) though you can select it using MDX.

Answer 2

Philip,

Absolutely I can query the set, but where does it hide? Anyone have any ideas how to make it visible?

 

 

thanks,

 

Rok

Answer 3

You can't, well not with client tools I listed. I think that this I'd actually an issue with the client tool more than anything. The set exists, how a client tool displays it is up to the client tool, though I concede that AS probably makes life difficult for client tools under the covers so to speak. I seem to recall AS2000 doing the se thing though I think ProClarity will show the named sets under a specific folder. What client tool are you using? Can you try ProClarity/Excel2010 or Panorama Novaview?

Answer 4

Excel 2007, Proclarity, mngmnt studio,and  owc for visual studio all displays only the first Set (single dimension members). I don't have excel 2010 yet.

 

thanks,

Rok

Answer 5

Hmmm, I'm afraid it is down to the client tool. The following C# code will enumerate all sets on an Analysis Services server where you will find the ones that are "hidden" I'm sure.

    public static void TabulateSets()
    {
      MSAS.Server asServer = new MSAS.Server();
      asServer.Connect("Provider=MSOLAP;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=<DB_NAME>;Data Source=<SERVER_NAME>");

      try
      {
        Console.WriteLine("Database,Cube,Set,DisplayFolder");
        
        foreach (MSAS.Database dbs in asServer.Databases)
        {
          foreach (MSAS.Cube cube in dbs.Cubes)
          {
            foreach (MSAS.MdxScript script in cube.MdxScripts)
            {
              foreach (MSAS.CalculationProperty prop in script.CalculationProperties)
              {
                if (prop.CalculationType == Microsoft.AnalysisServices.CalculationType.Set)
                {
                  Console.WriteLine("{0},{1},{2},{3}", dbs.Name, cube.Name, prop.CalculationReference, prop.DisplayFolder);
                }
              }
            }
          }
        }
      }
      finally
      {
        asServer.Disconnect();
      }
    }

Answer 6

Alternatively, assuming you have AS2008, just execute the following in SSMS in an MDX window:

 

select * from $system.mdschema_sets

Frank

 

Answer 7

".. I don't have excel 2010 yet. .." - the Excel 2010 Pivot Table Field List can show Sets with multiple dimensions in a folder separate from other dimensions. See the screenshot in this Excel team blog entry, which shows the “2008-09 Actual vs. Forecast” set, with both [Fiscal Year] and [Scenario Name] members:

PivotTable Named Sets in Excel 2010

---

- Deepak