Rendering Dynamic Chart using MDX and DDA

12-20-2009, 11:49 AM
Hello DDA mods,

I'm trying to evaluate a few products and I came across your DDA.  I just want to know if DDA can satisfy my following need.

I have to render a chart from an existing cube using an MDX created dynamically at run time.   Any pointers on how to achieve this would be nice.  I'm sure a simple sample application would benefit more than just me;

1) An MDX is passed.
2) Run the MDX against the cube to get the data
3) Draw the chart using the MDX above and pre-configured format, like size, color, measures, dimensions, hierarchy etc. This configuration can be from Database/xml.
4) Render dynamically-generated chart on the screen. (User Control or otherwise)

This chart can be used anywhere in the application.  This should all happen at run time.

Of note, I did download the WebViewer solution.  Doesn't seem like it does what I'm looking for.

Thanks,

InnoMan

Re: Rendering Dynamic Chart using MDX and DDA

12-20-2009, 4:40 PM
Hi InnoMan,

In short, as you suspected DDA isn't really that type of product.

The longer version, rather than taking an MDX query from the user/developer and displaying a chart for it; we basically have your taken the standard pivot table model and added many features on top of it. 

Each cell can now have various encodings applied to it that all you to see multiple variables in one cell at the same time.  These encodings allow you to turn your standard pivot table into a chart by allowing the user to specify which of the column or row shelf that a measure should be plotted against.  On its own this give you a bar chart or dot plot, but place a measure on each shelf and now you are able to see the data in an X/Y plot. 

Place an attribute or discrete measure on the color shelf and new data points will emerge colored based on the values, place a continuous measure on the color shelf and the current data points are now colored according to a scale (which can be adjusted).  Do the same on the size shelf and it will make the data points thicker for bars and larger for the others.

Use the pages shelf to show only one group of the data at a time.  Use the level of detail shelf to break the encoded values into their constituent parts.  Filter based not only on the standard operators, but easily decide what range of data to show or select individual members of an attribute.  Add a trendline to your plot to graphically show where the data is headed and visually see how well it fits the graph.

So in the end what we have is a pivot table with encoding and charting capabilities instead of a chart that can bind to a pivot table/multidimensional data source.

Thanks,

James

Re: Rendering Dynamic Chart using MDX and DDA

12-21-2009, 10:21 AM
James,

Looks like what you are saying can also be made feasible for our requirement.

Since I'm really not that familiar with your object model, do you have an example of what you are talking about?  I would love to get a sample solution from you which does what you are mentioning.

Do you think you can help me with that?

Thanks,

InnoMan

Re: Rendering Dynamic Chart using MDX and DDA

12-22-2009, 11:36 PM
InnoMan,

You can go through one of the samples in which the desired functionality has been accomplished. The default location of the sample is:
C:\Documents and Settings\UserName\My Documents\Data Dynamics\Analysis\1.0.726.0\Samples\CS\DataSourceSample

After running this sample, just go ahead and drag and drop Measures or Attributes to the Rows and Columns.
Amit Pal
GrapeCity- DataDynamics

Re: Rendering Dynamic Chart using MDX and DDA

12-23-2009, 1:33 PM
 Amit Pal wrote:
InnoMan, You can go through one of the samples in which the desired functionality has been
accomplished. The default location of the sample is: C:\Documents and Settings\UserName\My Documents\Data
Dynamics\Analysis\1.0.726.0\Samples\CS\DataSourceSample After running this sample, just go ahead and drag and drop Measures
or Attributes to the Rows and Columns.


Amit/James,

I went through the object model.   I like this object model better than other products I've evaluated so far for my needs.

I created a sample solution by consuming the already-created LocalCubeDataSource.

Since I can save and restore the layout, at this point, I am thinking I should stay away from my previous requirement of having to store an MDX.

This is what I'm planning on doing.  Please let me know if there is any problem with my approach here.  Also, please let me know if I'm thinking something that is completely out of reach of DDA.

I am planning on having 2 pages.  All controls on both of these will be dynamically generated.
- Chart Designer Page
- Chart Display Page

The reason all this has to happen automatically is that the entire application is automated.  We do not have a page UI designer in our application. 

Designer - Save Settings Page:
1> Load Cube (**?1**)
    a) Create DDADS from the connection string stored, dynamically
    b) create ddacube from ddads above, dynamically
2> Create PivotView, dynamically
3> Set the datasource of the PivotView cube created dynamically from database.
4> Show PivotView in a way the user can customize the way s/he wants to view the report using the following settings.
            localPivotView.AppearanceSettings.SchemaVisible = true;
            localPivotView.AppearanceSettings.ToolbarVisible = true;
            localPivotView.CardLayout.AutoShowLegends = true;
- Save layout

Display Saved Chart - Page (similar to a Dashboard):
1> Retrieve saved Layout (**?2**)
    a) Create DDADS from the connection string stored dynamically
    b) create ddacube from ddads above dynamically
2) Load Layout of the PivotView(s)
3> Show PivotView(s) with the following settings.
            localPivotView.AppearanceSettings.SchemaVisible = false;
            localPivotView.AppearanceSettings.ToolbarVisible = false;
            localPivotView.CardLayout.AutoShowLegends = false;

**?1**) How do I create a .ddads/.ddacube/.schema based on connection string on the fly? I did not find any documentation on how to do this.

**?2**) Is it possible to load a layout from a .ddads/.ddacube dynamically?  If so, how can I achieve this?

Thanks,

InnoMan.

Re: Rendering Dynamic Chart using MDX and DDA

12-24-2009, 5:00 AM
Hello,


To generate a cube, you will need to first connect to your relational datasource using the RdDataSource, XMLDataSource, or UnboundDataSource objects. You can then save a local cube of the data by using the SaveLocalCube method of the datasource to save a ddAnalysis cube file. Please go through the given below links for more details:

http://www.datadynamics.com/Help/ddAnalysis/DataDynamics.Analysis.Windows.Forms~DataDynamics.Analysis.DataSources.RdDataSource.html

http://www.datadynamics.com/Help/ddAnalysis/DataDynamics.Analysis.Windows.Forms~DataDynamics.Analysis.DataSources.UnboundDataSource.html

http://www.datadynamics.com/Help/ddAnalysis/DataDynamics.Analysis.Windows.Forms~DataDynamics.Analysis.DataSources.XmlDataSource.html

http://www.datadynamics.com/Help/ddAnalysis/DataDynamics.Analysis.Windows.Forms~DataDynamics.Analysis.DataSources.IPersistLocalCube~SaveLocalCube.html


The cube files can be saved by calling the SaveLocalCube method from an RdDataSource, UnboundDataSource, or XmlDataSource object. For example, you could use the following line of code to save the cube:
rdDataSource1.SaveLocalCube(new System.IO.FileInfo("C:\\cubefile.ddacube"), DataDynamics.Analysis.DataSources.SaveLocalCubeOptions.None);

Then to load the cube you would need to have a LocalCubeDataSource object and provide it with the path for the cube file. Once the LocalCubeDataSource object is setup you should be able to attach it to the pivot view by setting the pivot view's datasource to the LocalCubeDataSource object. This could be done in code by using something similar to:
LocalCubeDataSource1.LocalCubeFile = "C:\\cubefile.ddacube";
this.pivotView1.DataSource = LocalCubeDataSource1;
LocalCubeDataSource1.Connect();

Or on the web you could also use the following with a LocalCubeDataSource added to the aspx page:
this.LocalCubeDataSource1.LocalCubeFile = "C:\\cubefile.ddacube";
this.pivotView1.DataSourceID = this.LocalCubeDataSource1.ID;

To know how to create a Schema we have a sample (SchemaGeneration) installed with DataDynamics Analysis which demonstrate how you can create schema at run time. You may find these samples installed at the following location:

C:\Documents and Settings\UserName\My Documents\Data Dynamics\Analysis\xx.xx.xx\Samples\CS\SchemaGeneration
(Where xx.xx is the build installed on your machine)

Along with this we have several other samples as well which shows different features of DataDynamics Analysis.

Let me know if it helps or you need further assistance.

Thanks,
Gaurav

Re: Rendering Dynamic Chart using MDX and DDA

12-28-2009, 4:09 AM

InnoMan,

.ddads files are shared data sources files (they are used in Viewer and other samples, for example). You can create your own shared datasources by using WriteDataSource method.

Thank you,
Andrey T.

Re: Rendering Dynamic Chart using MDX and DDA

12-28-2009, 7:50 AM
 GauravC wrote:
Hello, To generate a cube, you will need to first connect to your relational datasource using the RdDataSource, XMLDataSource, or UnboundDataSource objects. You can then save a local cube of the data by using the SaveLocalCube method of the datasource to save a ddAnalysis cube file. Please go through the given below links for more details: http://www.datadynamics.com/Help/ddAnalysis/DataDynamics.Analysis.Windows.Forms~DataDynamics.Analysis.DataSources.RdDataSource.html http://www.datadynamics.com/Help/ddAnalysis/DataDynamics.Analysis.Windows.Forms~DataDynamics.Analysis.DataSources.UnboundDataSource.html http://www.datadynamics.com/Help/ddAnalysis/DataDynamics.Analysis.Windows.Forms~DataDynamics.Analysis.DataSources.XmlDataSource.html http://www.datadynamics.com/Help/ddAnalysis/DataDynamics.Analysis.Windows.Forms~DataDynamics.Analysis.DataSources.IPersistLocalCube~SaveLocalCube.html The cube files can be saved by calling the SaveLocalCube method from an RdDataSource, UnboundDataSource, or XmlDataSource object. For example, you could use the following line of code to save the cube: rdDataSource1.SaveLocalCube(new System.IO.FileInfo("C:\\cubefile.ddacube"), DataDynamics.Analysis.DataSources.SaveLocalCubeOptions.None); Then to load the cube you would need to have a LocalCubeDataSource object and provide it with the path for the cube file. Once the LocalCubeDataSource object is setup you should be able to attach it to the pivot view by setting the pivot view's datasource to the LocalCubeDataSource object. This could be done in code by using something similar to: LocalCubeDataSource1.LocalCubeFile = "C:\\cubefile.ddacube"; this.pivotView1.DataSource = LocalCubeDataSource1; LocalCubeDataSource1.Connect(); Or on the web you could also use the following with a LocalCubeDataSource added to the aspx page: this.LocalCubeDataSource1.LocalCubeFile = "C:\\cubefile.ddacube"; this.pivotView1.DataSourceID = this.LocalCubeDataSource1.ID; To know how to create a Schema we have a sample (SchemaGeneration) installed with DataDynamics Analysis which demonstrate how you can create schema at run time. You may find these samples installed at the following location: C:\Documents and Settings\UserName\My Documents\Data Dynamics\Analysis\xx.xx.xx\Samples\CS\SchemaGeneration (Where xx.xx is the build installed on your machine) Along with this we have several other samples as well which shows different features of DataDynamics Analysis. Let me know if it helps or you need further assistance. Thanks, Gaurav


Gaurav,

I'm doing all this on the web.  Do you have any samples for doing all this on web?

Thanks,

Re: Rendering Dynamic Chart using MDX and DDA

12-28-2009, 2:44 PM
Gaurav,

Can you please help me with the attached solution?  I'm stuck at the place where I'm creating the PivotView and RDDataSource dynamically.

There 4 web pages in the solution, 2 are design time pages, and 2 are run-time pages.  Both solutions use AdventureWorks DW as their base.

Design Time Pages: These two seem to work fine.
1) DeisignTimeChartDesigner.aspx
2) DesignTimeChartViewer.aspx

Now I'm trying to do the same thing at run time in the following pages
1) RuntimeChartDesigner.aspx
2) RuntimeChartViewer.aspx

The following is the code that I'm using to create the view and the data source.

            DataDynamics.Analysis.Web.PivotView view = new DataDynamics.Analysis.Web.PivotView();
            DataDynamics.Analysis.Web.DataSources.RdDataSource RDDs = new DataDynamics.Analysis.Web.DataSources.RdDataSource();
            RDDs.ID = "TestId";
            RDDs.CustomSchemaFile = App_Code.GenerateRelationalSchema(TextBoxSQL.Text, TextBoxConnectionString.Text);
            view.DataSourceID = RDDs.ID;
            ChartPlaceHolder.Controls.Add(view);

When I hit "Connect 1" button in "RuntimeChartDesigner.aspx", I'm getting this exception

[NullReferenceException: Object reference not set to an instance of an object.] DataDynamics.Analysis.Web.PivotView.#XHi(HttpSessionState session, String instanceKey) +120 DataDynamics.Analysis.Web.PivotView.#obf() +42 DataDynamics.Analysis.Web.PivotView.get_AppearanceSettings() +37 DataDynamics.Analysis.Web.PivotView.#0Hi(Page page) +397 DataDynamics.Analysis.Web.PivotView.OnPreRender(EventArgs e) +116 System.Web.UI.Control.PreRenderRecursiveInternal() +80 System.Web.UI.Control.PreRenderRecursiveInternal() +171 System.Web.UI.Control.PreRenderRecursiveInternal() +171 System.Web.UI.Control.PreRenderRecursiveInternal() +171 System.Web.UI.Control.PreRenderRecursiveInternal() +171 System.Web.UI.Control.PreRenderRecursiveInternal() +171 System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +842

Please help!

Both the design time and run time pages should do the same thing.

1) A page where you design the charts
2) A page where you view the charts.

Thanks,

 GauravC wrote:
Hello, To generate a cube, you will need to first connect to your relational datasource using the RdDataSource, XMLDataSource, or UnboundDataSource objects. You can then save a local cube of the data by using the SaveLocalCube method of the datasource to save a ddAnalysis cube file. Please go through the given below links for more details: http://www.datadynamics.com/Help/ddAnalysis/DataDynamics.Analysis.Windows.Forms~DataDynamics.Analysis.DataSources.RdDataSource.html http://www.datadynamics.com/Help/ddAnalysis/DataDynamics.Analysis.Windows.Forms~DataDynamics.Analysis.DataSources.UnboundDataSource.html http://www.datadynamics.com/Help/ddAnalysis/DataDynamics.Analysis.Windows.Forms~DataDynamics.Analysis.DataSources.XmlDataSource.html http://www.datadynamics.com/Help/ddAnalysis/DataDynamics.Analysis.Windows.Forms~DataDynamics.Analysis.DataSources.IPersistLocalCube~SaveLocalCube.html The cube files can be saved by calling the SaveLocalCube method from an RdDataSource, UnboundDataSource, or XmlDataSource object. For example, you could use the following line of code to save the cube: rdDataSource1.SaveLocalCube(new System.IO.FileInfo("C:\\cubefile.ddacube"), DataDynamics.Analysis.DataSources.SaveLocalCubeOptions.None); Then to load the cube you would need to have a LocalCubeDataSource object and provide it with the path for the cube file. Once the LocalCubeDataSource object is setup you should be able to attach it to the pivot view by setting the pivot view's datasource to the LocalCubeDataSource object. This could be done in code by using something similar to: LocalCubeDataSource1.LocalCubeFile = "C:\\cubefile.ddacube"; this.pivotView1.DataSource = LocalCubeDataSource1; LocalCubeDataSource1.Connect(); Or on the web you could also use the following with a LocalCubeDataSource added to the aspx page: this.LocalCubeDataSource1.LocalCubeFile = "C:\\cubefile.ddacube"; this.pivotView1.DataSourceID = this.LocalCubeDataSource1.ID; To know how to create a Schema we have a sample (SchemaGeneration) installed with DataDynamics Analysis which demonstrate how you can create schema at run time. You may find these samples installed at the following location: C:\Documents and Settings\UserName\My Documents\Data Dynamics\Analysis\xx.xx.xx\Samples\CS\SchemaGeneration (Where xx.xx is the build installed on your machine) Along with this we have several other samples as well which shows different features of DataDynamics Analysis. Let me know if it helps or you need further assistance. Thanks, Gaurav

Re: Rendering Dynamic Chart using MDX and DDA

12-29-2009, 12:28 AM

InnoMan,

First of all, if you are adding data source at run-time, you should use DataDynamics.Analysis.DataSources instead of DataDynamics.Analysis.Web.DataSources (Web.DataSources.xxx is a control which is used at design time). Also, add PivotView to page before setting its DataSource property. This code snippet is working good:

            DataDynamics.Analysis.Web.PivotView pView = new DataDynamics.Analysis.Web.PivotView();

            DataDynamics.Analysis.DataSources.RdDataSource RDDs = new DataDynamics.Analysis.DataSources.RdDataSource();
            RDDs.CustomSchemaFile = App_Code.GenerateRelationalSchema(TextBoxSQL.Text, TextBoxConnectionString.Text);
            RDDs.QueryString = TextBoxSQL.Text;
            RDDs.ConnectionString = TextBoxConnectionString.Text;
            RDDs.ConnectionType = ConnectionType.xxx; //specify connection type here
           
            RDDs.Connect();
                   
            ChartPlaceHolder.Controls.Add(pView);

            pView.DataSource = RDDs;

Thank you,
Andrey T.