Blog

Using FlatDataSourceGrouping - XamPivotGridGrouping Series - Post 2

Brian E. Roach / Thursday, April 18, 2013

XamPivotGridGrouping 2 - Providing the Data

As I mentioned in my last post, we are going to make use of the XamPivotGridGrouping control to create a new WPF Football Statistics Analysis application.  We'll use the XamPivotGridGrouping to showcase our data and summarize it based on a variety of different data points.  To do this, we're going to need a data source. 

Just like the XamPivotGrid, the XamPivotGridGrouping supports the following data sources:

  • SSAS (via GroupingFlatDataModelProvider)
    • XmlaDataSource
      • The XmlaDataSource allows connecting and executing queries against SSAS through XML for Analysis model provider.
    • AdomdDataSource
      • The AdomdDataSource allows connecting and executing queries directly against SSAS
  • SAP NetWeaver Business Warehouse (via GroupingFlatDataModelProvider)
    • XmalSapDataSource
      • The XmalSapDataSource provides a way to connect and execute queries against SAS through XMLA
  • Oracle Essbase (via GroupingFlatDataModelProvider)
    • XmlaOracleDataSource
      • The XmlaOracleDataSource provides a way to connect and execute queries against Essbase for Oracle through XMLA.
  • IEnumberable data collection
    • (GroupingFlatDataSource)FlatDataSource
      • The FlatDataSource, or in this case GroupingFlatDataSource, provides a way to use System.Collections.IEnumerable as data source to display data.

Now, for no better reason than that it's simple and straightforward, we are going to use the GroupingFlatDataSource controller.
We'll use it for both our GroupingDataSelector and our XamPivotGridGrouping control.

From our perspective, the way data binding works for the XamPivotGridGrouping is shown here:

For the GroupingFlatDataSource, the ItemSource can be any object that implements IEnumerable.

For our sample, we are going to base everything on an object called BaseStatisticTotalResult which implements INotifyPropertyChanged.
The class diagram is shown here (I hid the Fields Compartment for brevity):

Our data source will be built off an IEnumerable.
The data itself is stored in a SQL Server db which I've added to hold the stat data.  I'm using Entity to connect, and we'll be calling some stored procedures via Function Imports to aquire the data. 

I have a Utility class called FlatDataConstructr which we'll use to generate the IEnumerable and pass it back to the ViewModel which handles Data in our project.  This snippet shows the OffensiveStatisticsAllTeams() method of FlatDataConstructor.

public IEnumerable<BaseStatisticTotalResult> OffensiveStatisticsAllTeams()
     {
           m_offensiveAll = new List<BaseStatisticTotalResult>();
           var items = GetAllStatsSQL();
           int counter = 0; 
          try
           {
                 foreach(SQLStatisticResultObject o in items)
                {
                    // For each Resultant Item from our above Entity Call,
                    // create aBaseStatisticTotalResult and add it to theLocalList
                    BaseStatisticTotalResult stat = new BaseStatisticTotalResult
                    {
                         Game = GetGameDescription2(o.AwayTeam ,o.HomeTeam, (int) 
                                                    o.AwayScore, o.HomeScore),
                         Player = o.FName + " " + o.LName,
                         Position = o.Position,
                         PositionType = o.PosType,
                         Season = (int)o.Season,
                         Statistic = o.StatResult,
                         ResultID = (Int64)o.ResultID,
                         StatisticTotal = o.ResultTotal,
                         Team = o.Name,
                         StatSubType = o.StatSubType,
                         StatType = o.StatType,
                         Height = o.HEIGHT,
                         Weight = o.WEIGHT.ToString(),
                         College = o.College,
                         Number = o.Number,
                         QuarterValue = (int)o.Quarter,
                         PlayerID = (Int64)o.PlayerID,
                         GameID = (Int64)o.GameID
                  };
// Some of the properties ofBaseStatisticTotalResult are not directly mapped to a field 
// in the result of the Entity query. So additional work is needed. Above this is done using
// GetGameDescription2 which returns a description of the Game and below the Opposing Team is 
// determined by comparing thePlayerTeam to the Home and Away Teams of the game.
              stat.OpposingTeam = o.PlayerTeam != o.HomeTeam ? o.HomeTeam : o.AwayTeam;
              counter++;
              m_offensiveAll.Add(stat);
            }
        }
        catch (Exception exception)
        {
              if (exception.Message.Length > 0)
              {
                   // Handle exceptions here.
              }
              throw;
        }
// These are special case statistics for Passer Rating,Avg Yards, Comp% etc.
        AddRatingsStatsForQB(m_offensiveAll);
        AddPrecentageStatsForPlayer(m_offensiveAll);
        return m_offensiveAll;
  }

As you can see in this snippet, we return a List<BaseStatisticTotalResult> as IEnumberable. 

The ViewModel which is in charge of generating our OLAP cube, and defining the Dimensions, Measures and Hierarchies is called DataConstructorViewModel.  Here is a abbreviated class structure for this viewmodel:

 

The specifics of the class are as follows:

  • m_dataConstructor is an instance of FlatDataConstructor
  • m_oStatsAllTeamsFlat is an instance of GroupingFlatDataSource
  • OffensiveStaticstAllTeamsData is a public GroupingFlatDataSource 

What we'll eventually be doing is using our OffensiveStaticstAllTeamsData as the data source for our XamPivotGridGrouping and it's associated XamDataSelectorGrouping.

To close out our look at building the data source, let's examine the methods in the DataConstructorViewModel and see how each one is being used to build out the final GroupingFlatDataSource.

Let's start with OffensiveStatisticsAllTeams:

        public void OffensiveStatisticsAllTeams()
        {

            if (m_allTeamsOStatsList= null) m_allTeamsOStatsList = new List<BaseStatisticTotalResult>();
            if (m_dataConstructor= null) m_dataConstructor = newFlatDataConstructor();
            m_allTeamsOStatsList m_dataConstructor.OffensiveStatisticsAllTeams().ToList();
            m_allTeamsOStatsList.GetEnumerator();
           
            var statsDataSource = new GroupingFlatDataSource
            {
                ConnectionSettings new GroupingConnectionSettings(),
                ItemsSource = m_allTeamsOStatsList,
                Cube = DataSourceBase.GenerateInitialCube("Cube"),
                Columns = DataSourceBase.GenerateInitialItems( "[Season].[Season], [Game].[Game], " +
                                                                [Half].[Half], [Quarter].[Quarter]"),
                Rows = DataSourceBase.GenerateInitialItems(
                        "[Player].[Player], [Team].[Team], [Position].[Position],
                          [StatType].[StatType],[StatSubType].[StatSubType], [Statistic].[Statistic]"),
                Measures = DataSourceBase.GenerateInitialItems("StatisticTotal")
            };

            DefineOffensiveDimensionMetadata(statsDataSource);
            SetFlatDataProperties(statsDataSource);

            m_oStatsAllTeams = statsDataSource;
       }

Let's break down this method a little.  The beginning of the method is fairly self-explanitory as we are just declaring the List and an instance of the FlatDataConstructor.  We then call the OffensiveStatisticsAllTeams method to populate our list.  So we start dealing with our GroupingFlatDataSource beginning with this snippet:

      var statsDataSource = new GroupingFlatDataSource
            {
                ConnectionSettings new GroupingConnectionSettings(),
                ItemsSource = m_allTeamsOStatsList,
                Cube = DataSourceBase.GenerateInitialCube("Cube"),
                Columns = DataSourceBase.GenerateInitialItems("[Season].[Season], [Game].[Game], " +
                                                              "[Half].[Half], [Quarter].[Quarter]"),
                Rows = DataSourceBase.GenerateInitialItems(
                        "[Player].[Player], [Position].[Position], [StatType].[StatType], " +
                        "[StatSubType].[StatSubType], [Statistic].[Statistic]"                
                RowsGroups = DataSourceBase.GenerateInitialItems("[Team].[Team]"               
                Measures = DataSourceBase.GenerateInitialItems("StatisticTotal")
            };

Because we are simply using a IEnumberable data source, we can pass in an empty GroupingConnectionSettings.  However, if we were pulling directly from SQL or another data source we would need to implement them here.

ItemsSource is, of course, the list we just generated from the FlatDataConstructor. 

Cube uses the DataSourceBase.GenerateInitialCube() method to generate an Infragistics.Olap.Data.ICube that can be passed to Cube property as the default cube.

For Columns, Rows and RowsGroups we use DataSourceBase.GenerateInitialItems to set the initial set of IFilterViewModel items whicfor each of these respective collections.

Measures uses the GenerateInitialItems method to set the collection of IMeasureViewModel items for our model.

This snippet creates our GroupingFlatDataSource.  The next two lines call methods which assist us in refining that datasource.

DefineOffensiveDimensionMetadata(statsDataSource);
SetFlatDataProperties(statsDataSource);

Let's look at each of these methods:

private static void DefineOffensiveDimensionMetadata(GroupingFlatDataSource flatDataSource)
{
       var modelMetadata = new CubeMetadata();
       flatDataSource.AllowRemainderTotalForColumns = true;
       flatDataSource.UseZerosOnEmptyTotals = true;
       modelMetadata.DataTypeFullName = typeof(BaseStatisticTotalResult).FullName;
       modelMetadata.DisplayName =
                                   Strings.Label_DataConstructorViewModelDataFields;

// Add Base Result Hierarchies - By arranging Members of a Dimension into a hierarchy,
// not only do we determine the aggregation of Dimension members,
// but also simple mathematical summation or addition can be eliminated since the values
// for each members will automatically sum up or total to its parent
// aggregate member. 
       AddBaseHierarchies(flatDataSource);

// Add Base Dimensions - Dimensions are the business parameters normally seen in the rows
// and columns of a report.
       AddBaseDimensions(modelMetadata);

// Add Model Metadata toCubeSettings
       flatDataSource.CubesSettings.Add(modelMetadata);

// Adding this to set the Parent of expandable columns to be place in front of children.
        flatDataSource.ParentInFrontForColumns = true;

}

Initially in this method we create an object called CubeMetadata.  In other data sources, the data my already be organized into cubes, and each dimension may already have defined hierarchies.  This isn't the case when we use a FlatData source.  With FlatData, we can define the hierarchies for each property of our BaseStatisticTotalResult.  The last two method calls within this method will do just that.

After declaring and instantaiting our CubeMetada,  we set a couple additional properties on the GroupingFlatDataSource.  The key property for us right now is AllowRemainderTotalForColumns.  Setting this to true allows our columns to provide a "remainder".  This means if a record value is assigned to a parent column but not a child column, the remainder value will be displayed beneath that column.  

As an example, say we had a running back that played in 4 games during the 2011 Season.  He had statistics for each of those games.  For whatever reason, let's say we could only find associations for our statistics to 5 games, even though we had all the stats for the full season.  If we expanded the season out..those values for statistics not assigned to a game would be considered the remainder, and would appear beneath the parent (Season) column.  

The UseZeroesOnEmptyTotalForColumns, DataTypeFullName and DisplayName properties are self-explanatory.  (Note: In my strings resource, the value for the Label_DataConstructorViewModelDataFields isData Fields).

The AddBaseDimensions and AddBaseDimensions methods finish off this method.  As previously mentioned, these methods will allows us to define our own dimension and hierarchies for our BaseStatisticTotalResult. 

For each method, I'll show a snippet that is defining a hierarchy for two of the cube’s properties. The cube is the class specified in the data source as the main data holder. We can efine as many HierarchyLevelDescriptors as needed.

Here are the the two listed methods (I have removed some of the hierarchies for brevity) and an additional helper method to add Dimensions.

private static void AddBaseHierarchies(GroupingFlatDataSource flatDataSource)
{
    …
    var tHierarchy = newHierarchyDescriptor<BaseStatisticTotalResult>(p => p.Player);
    tHierarchy.AddLevel(p => p.Player, "Player" );
    tHierarchy.HierarchyName = "Player";  
    tHierarchy.HierarchyDisplayName = Strings.Label_OffensivePlayer; 
    flatDataSource.HierarchyDescriptors.Add(tHierarchy);

    var teamHierarchy = newHierarchyDescriptor<BaseStatisticTotalResult>(p => p.Team);
    teamHierarchy.AddLevel (p => p.Team, "Team" );
    teamHierarchy.HierarchyName = "Team";
    teamHierarchy.HierarchyDisplayName = Strings.Label_Team;
    flatDataSource.HierarchyDescriptors.Add(teamHierarchy);
    …
}

private static void AddBaseDimensions(CubeMetadata modelMetadata)
{
     AddDimensionData(modelMetadata, "Player" ,Strings.Label_OffensivePlayer ,
             DimensionType.Dimension);
     AddDimensionData(modelMetadata, "StatType" , "Type" ,
             DimensionType.Dimension);
     AddDimensionData(modelMetadata, "OpposingTeam",
             "OpposingTeam",DimensionType.Dimension);
     AddDimensionData(modelMetadata, "Quarter" , "Quarter" ,DimensionType.Dimension);
     AddDimensionData(modelMetadata, "Half" , "Half" ,DimensionType.Dimension);
     AddDimensionData(modelMetadata, "StatSubType", "Category" ,DimensionType.Dimension);
     AddDimensionData(modelMetadata, "Height" , "Height" ,DimensionType.Dimension);
     AddDimensionData(modelMetadata, "Weight" , "Weight" ,DimensionType.Dimension);
     AddDimensionData(modelMetadata, "College" , "College" ,DimensionType.Dimension);
     AddDimensionData(modelMetadata, "Game" ,Strings.Label_Game , 
              DimensionType.Dimension);
     AddDimensionData(modelMetadata, "Team" ,Strings.Label_Team ,DimensionType .Dimension);
     AddDimensionData(modelMetadata, "Position" ,Strings.Label_Position ,
              DimensionType.Dimension);
     AddDimensionData(modelMetadata, "PositionType",Strings .Label_PositionType ,
              DimensionType.Dimension);
     AddDimensionData(modelMetadata, "Season" ,Strings.Label_Season , 
              DimensionType.Dimension);
     AddDimensionData(modelMetadata, "Games" ,Strings.Label_GamesPlayed ,
              DimensionType.Dimension);
     AddDimensionData(modelMetadata, "GamesStarted" ,Strings.Label_GamesStarted ,
              DimensionType.Dimension);
     ...
     modelMetadata.DimensionSettings.Add(resultTotal);
}


private static void AddDimensionData(CubeMetadata model, string source, string display,
                     DimensionType type, IAggregator aggregator = null , string format = null)
{
     var dim = newDimensionMetadata
     {
           SourcePropertyName = source,
           DisplayName = display,
           DimensionType = type
      };
     if (aggregator != null)
     {
           dim.Aggregator = aggregator; 
     }
     if (format != null)
    {
          dim.DisplayFormat = format;
     }
    model.DimensionSettings.Add(dim);
}

For the Hierarchies, LevelExpressionPath is the property on which to group the data. LevelName will appear in the tree with hierarchies in the data selector.

For the Dimenseions, DisplayName is used to change the name of the hierarchy/measure in the UI and the DisplayFormat is used for measures. Raw value goes to the Value property of the cell and the formatted value goes to the FormattedValue property. 

At the end of the method we set one more property, which I set aside only for the purposes of highlighting it. This property, ParentInFrontForColumns, impacts how the expanding columns will appear.  Set to true, and the parent columns appear in front of the children, set to false, they appear behind the children.   

The last method we have to look at simply sets a few more properties on the GroupingFlatDataSource and is called SetFlatDataProperties.

private static void SetFlatDataProperties(GroupingFlatDataSourceflatDataSource)
{ 
     flatDataSource.MergeHierarchySettings.AllowMergeOfColumns = true;
     flatDataSource.DimensionsGenerationMode = DimensionsGenerationMode.Mixed;
} 

AllowMergeOfColumns needs to be set to true to allow our expanding and collapsing columns to merge together. The DimensionsGenerationMode enum is defined as follows:

Metadata       

The dimensions are created based on the CubeMetadata.DimensionSettings found in FlatDataSource.CubesSettings.
Mixed

Both data sources are used as data found in CubeMetadata.DimensionSettings takes precedence.
Property

The dimensions are created based on the properties exposed by the class of items found in items source.

With that, we have a GroupingFlatDataSource which is ready to be handed off to the XamPivotGridGrouping!  In the next post, we'll take a look at the results of this data, and start working on customizing the control to fit the requirements we set out.