Hi all,
I managed to retrieve relational data (from SQL Server) via FlatDataSource and to show them in a WinPivotGrid.
Yet, some attributes in my table are mis-recognised as measures: they are integer values, but aggregating them is of no use. So, these are not measures but dimensions!
It seems to me that all numeric values are considered as measures and all not-numeric values are considered as dimensions. This is just a guess, because an experimental cast to varchar for my integer attributes caused WinPivotGrid to offer them as dimensions. Back to integers, they were offered as measures...
Is there way to influence that? It should be me who decides which field is a measure and which is a dimension. :)
Thanks for any hint.
Martin
Hello Martin,
There is a possibility of changing measures to hierarchies by handling the InitializeOlapAxisElementDescriptor event and using the OlapElementDescriptor property of the event arguments.
Please refer to the comments of OlapElementDescriptor on how to use this property.
#region OlapAxisElementDescriptor/// <summary>/// Returns or sets a reference to the OLAP element to be associated with this instance's/// <see cref="Infragistics.Olap.FlatData.OlapAxisElementDescriptorEventArgs.PropertyDescriptor">PropertyDescriptor</see>./// </summary>/// <remarks>/// <p class="body">/// At the entry point for a handler of this event, this property returns either a/// <see cref="Infragistics.Olap.FlatData.HierarchyDescriptor">HierarchyDescriptor</see>/// or/// <see cref="Infragistics.Olap.FlatData.MeasureDescriptor">MeasureDescriptor</see>/// instance, depending on whether the data type of the class member it represents/// is numeric (members whose type is numeric (int, double, decimal, etc.) are assumed/// to represent a/// <see cref="Infragistics.Olap.Core.Data.Measure">Measure</see>)./// </p>/// <p class="body">/// Setting this property to null effectively removes the corresponding class member/// from the data source, i.e., no axis element is created for that member./// </p>/// <p class="body">/// The default behavior, whereby numeric types become measureDescriptors and non-numeric types/// become hierarchies, can be overriden by simply assigning an instance of the desired/// type to this property. When a class member whose type is non-numeric is/// designated as a Measure, however, the default aggregation method is undefined,/// and cells for that measure appear empty by default. This behavior can be overridden/// by handling the/// <see cref="Infragistics.Olap.FlatData.FlatDataSource.AggregateMeasure">AggregateMeasure</see>/// event, and manually aggregating the items therein./// </p>/// </remarks>public OlapAxisElementDescriptorBase OlapElementDescriptor{ get; set;}#endregion OlapAxisElementDescriptor
Please let me know if I may be of further assistance.
Sincerely,Sahaja KokkalagaddaAssociate Software Developer
Yep, it works:
//Handler
void ds_InitializeOlapAxisElementDescriptor(object sender, InitializeOlapAxisElementDescriptorEventArgs e)
{
switch (e.PropertyDescriptor.Name)
case "MyDimension":
e.OlapElementDescriptor = new HierarchyDescriptor(e.PropertyDescriptor);
break;
case "MyMeasure":
e.OlapElementDescriptor = new MeasureDescriptor(e.PropertyDescriptor);
}
Do you know any way to accelerate the row and column layouting? In my case, I've got a flat SQL Server table of ~200'000 rows; my dimension has 365 distinct values which scatter across these rows. In a database, I'd use an index, but in here....? Row buildup takes minutes...
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:
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]
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; } } }}
Hope you can rebuild it.
Thank you for sending us the above information. I will create a sample with this code to observe the mentioned performance issue in PivotGrid. I will provide you more information by end of Thursday.
Please let me know if you have any questions.
thanks for your enquiry.
I did some testing myself, too. I found out that there must be some connection with the explicit assignment of the "dimension role" to my mis-recognized-as-measure numeric field named MyDimensionA.
When I casted this numeric attribute explicitly to a varchar in the SQL Statement in the C# code I posted, everything worked as expected, and even very fast:
sb.AppendLine("SELECT cast([MyDimensionA] as varchar(4)) AS MyDimensionA, [MyDimensionB], [MyDimensionC], [MyMeasure] FROM TestWinPivotGrid");
Without casting, MyDimensionA remains integer and makes the waiting wheel turn infinitely when this field is dragged to the row drop area:
sb.AppendLine("SELECT [MyDimensionA], [MyDimensionB], [MyDimensionC], [MyMeasure] FROM TestWinPivotGrid");
Assigning the dimension "role" seems not to be enough. Maybe this can help investigation.
Regards
Hi Martin,
I followed the steps you suggested and was unable to reproduce the behavior you're describing. I changed the type of DimentionA which in my sample is Sports to both integer and string and did not observe any performance lag in both scenarios.I have attached the sample project I used to test this. Please test this project on your PC; whether or not it works correctly may help indicate the nature of this problem.I have used an enumerable list as my DataSource as you mentioned fetching data is taking only two seconds. So I assumed this is not related to the DataBase and used a simple FlatDataSource sample.If the project does not work correctly, this indicates either a problem possibly specific to your environment, or a difference in the DLL versions we are using. My test was performed using version 2016.20162.1000 in Infragistics for WindowsForms 2016 Vol2.If this sample project is not an accurate demonstration of what you're trying to do, please feel free to modify it and send it back.
Please let me know if I can provide any further assistance.
it must be the DLLs:
I've got the 16.1 versions, so I had to switch all references in your project to my 16.1-dlls. Then it compiled, but showed the same problems in your project as I had in my approach.
:(
Do you have a 16.1 version at hand to reproduce my issue? Maybe I can convince our IT dept to upgrade to 16.2, but this takes nearly as long as the eternal loop in my form :D.
Thanks and have nice weekend.
PS: Exact Version as in object Explorer: System.Reflection.AssemblyFileVersionAttribute("16.1.20161.1000"),
I tested the PivotGrid sample using 16.1.1000 build and observed some performance lag. However, the steps I followed are a bit different. I was able to drag the dimensions with no trouble but I’m not able to expand them. I did not reproduce this issue using 16.1 SR though. My recommendation for resolving this issue is to get 16.1 SR (16.1.20161.2118) and see if that resolves this issue.
If you have a 16.1 key then the SR download link can be found in your MyKeysandDownloads page when you log into your account at Infragistics.com.
Please try the above suggestion and let me know if I may be of further assistance.
the IT department won't let me install this on my own. And introducing a new version of any software here is quite an ordeal :)
So, I'm stuck for now.
I'm gonna ask the IT guys. Maybe this new version will come true for my Company in this life. *g*
Thanks for your patient help.
Sure. You can contact us back if you need any assistance after upgrading.
Thank you for choosing Infragistics.