Recognition of measures and dimensions in FlatDataSource

Hello Sahaja,

thanks for your offer.

As I don't see a way to upload any files here in the forum, I'll describe how to reproduce my problem:

DBMS is SQL Server 2008 R2
My table layout is:

CREATE TABLE [dbo].[TestWinPivotGrid]

(

[ID] int,

[MyDimensionA] [varchar](2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[MyDimensionB] [smallint] NULL,

[MyDimensionC] [varchar](12) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[MyMeasure] [decimal](19, 3) NULL,

CONSTRAINT [PK_TestWinPivotGrid] PRIMARY KEY CLUSTERED

(

[ID] ASC

)

WITH

(

PAD_INDEX = OFF,

STATISTICS_NORECOMPUTE = OFF,

IGNORE_DUP_KEY = OFF,

ALLOW_ROW_LOCKS = ON,

ALLOW_PAGE_LOCKS = ON

) ON [PRIMARY]

) ON [PRIMARY]
This table is filled with 200'000 rows. I can't copy them here :), but just fill them with some random data matching the data types. MyDimensionA has 365 different values, MyDimensionB has 6, MyDimensionC has 150'000.
My VS 2012 Project contains a form with a UltraPivotGrid and a OlapDataSelector. Its Code is:

using System;
using System.Text;
using System.Windows.Forms;
using Infragistics.Olap.FlatData;
using Infragistics.Olap.FlatData.Adapters.Sql;

namespace WinPivotGridDemo
{
    public partial class frmRelational : Form
    {
        public frmRelational()
        {
            InitializeComponent();
        }

        private void button1_Click(object sender, EventArgs e)
        {
            SetDataSource();
        }

        private void SetDataSource()
        {
            //http://help.infragistics.com/Help/Doc/WinForms/2016.1/CLR4.0/html/WinPivotGrid_Using_the_FlatDataSource_DataAdapter_classes.html
            Cursor = Cursors.WaitCursor;
            string connectionString = @"Data Source=myserver;Initial Catalog=mydatabase;Integrated Security=True";
            StringBuilder sb = new StringBuilder();
            sb.AppendLine("SELECT [MyDimensionA], [MyDimensionB], [MyDimensionC], [MyMeasure] FROM tblTestWinPivotGrid");
            FlatDataSourceInitialSettings settings = new FlatDataSourceInitialSettings();
            FlatDataSource ds = new FlatDataSource(settings);
            ds.InitializeOlapAxisElementDescriptor += ds_InitializeOlapAxisElementDescriptor; //handles decision measure/dimension, see below
            ds.Timeout = new TimeSpan(0,5,0);
            using (SqlAdapter adapter = new SqlAdapter(connectionString))
            {
                adapter.QueryString = sb.ToString();
                adapter.Fill(ds);
            }
            ultraPivotGrid1.SetDataSource(ds);
            olapDataSelector1.DataSource = ds;
            Cursor = Cursors.Default;
        }

        //Handler for decision measure/dimension
        void ds_InitializeOlapAxisElementDescriptor(object sender, InitializeOlapAxisElementDescriptorEventArgs e)
        {
            switch (e.PropertyDescriptor.Name)
            {
                case "MyDimensionA":
                    e.OlapElementDescriptor = new HierarchyDescriptor(e.PropertyDescriptor);
                    break;

                case "MyDimensionB":
                    e.OlapElementDescriptor = new HierarchyDescriptor(e.PropertyDescriptor);
                    break;

                case "MyDimensionC":
                    e.OlapElementDescriptor = new HierarchyDescriptor(e.PropertyDescriptor);
                    break;

                case "MyMeasure":
                    e.OlapElementDescriptor = new MeasureDescriptor(e.PropertyDescriptor);
                    break;
            }
        }
    }
}
Fetching the data (Button) takes just 2 seconds.
Dragging MyDimensionA (365 distinct values) to row, MyDimensionB (6 distinct values) to column, and MyMeasure to Measures starts the waiting wheel... it spins eternal...
I didn't dare to drag MyDimensionC to rows, as 150'000 distinct values would be even more work than 365...

Hope you can rebuild it.

Thanks for any hint.

Thanks! :D