Data Dynamics Analysis enables you to analyze data from any relational data source.
This walkthrough illustrates how to analyze relational data from a Microsoft Access database.
The walkthrough is split into the following activities:
- Create a schema that models your relational data
- Set up an Analysis project inside Visual Studio
- Connect the schema file to a relational data source
- Perform visual analysis against live data
To use this walkthrough, you will need access to the Reels relational database included with the installation.
When you are finished, your application will look similar to the following:
To create a schema that models your relational data
- From the Start menu, select Run...
- In the Open textbox, type cmd and click OK.
3. Redirect your current directory to the following location, replacing <build number> with the build that you installed on your machine:
cd "c:\Program Files\Data Dynamics\Analysis\<build number>\Bin"
4. Press Enter.
5. Copy the following command to your clipboard:
DataDynamics.Analysis.SchemaGenerator -c "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\Documents and Settings\<user name>\My Documents\Data Dynamics\Analysis\DataSources\Reels\Reels.mdb;Persist Security Info=False;" -q "SELECT s.SalesId, m.Title, m.MPAA, mt.Description AS Media, sd.Quantity, s.SaleDate, sd.Price, sd.Discount, (1-[sd].[Discount])*[sd].[Price]*[sd].[Quantity] AS SaleAmount, (((1-[sd].[Discount])*[sd].[Price])-[p].[StorePrice])*[sd].[Quantity] AS Profit, (1-[sd].[Discount])*[sd].[Quantity]*[sd].[Price]*([s].[TaxPercent]/100) AS TaxAmount FROM Movie AS m INNER JOIN (Sale AS s INNER JOIN ((Product AS p INNER JOIN (MediaType AS mt INNER JOIN MovieProduct AS mp ON mt.MediaID = mp.MediaType) ON p.ProductID = mp.ProductID) INNER JOIN SaleDetails AS sd ON p.ProductID = sd.ProductID) ON s.SalesID = sd.SaleID) ON m.MovieID = mp.MovieID;" "c:\DDAWalkthrough.schema"
6. Paste the contents from your clipboard into the command prompt, replacing <user name> with your user name.
Set up an Analysis project inside Visual Studio
To create a Windows Forms project
- Create a new Windows Application in Visual Studio and name the project DDAWalkthrough.
2. When the new project loads, navigate to the toolbox to expand the Data Dynamics Analysis category.
3. Drag and drop the RdDataSource control and the PivotView control onto your form.
4. Select the pivotView1 control on the Form.
1. Set the DataSource property to rdDataSource1.
2. Change the Dock property to Fill.
Connect the schema file to a relational data source
To connect your schema file to the relational data source
1. Select the rdDataSource1 component in the component tray.
2. In the Properties panel set the ConnectionString property to the following connection string, replacing the text <user name> with your user name on your machine:
Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\Documents and Settings\<user name>\My Documents\Data Dynamics\Analysis\DataSources\Reels\Reels.mdb;Persist Security Info=False;
3. Change the ConnectionType property to OleDb.
4. Set the CustomSchemaFile property to c:\DDAWalkthrough.schema.
5. Copy and paste the following SQL string into the QueryString property:
SELECT s.SalesId, m.Title, m.MPAA, mt.Description AS Media, sd.Quantity, s.SaleDate, sd.Price, sd.Discount, (1-[sd].[Discount])*[sd].[Price]*[sd].[Quantity] AS SaleAmount, (((1-[sd].[Discount])*[sd].[Price])-[p].[StorePrice])*[sd].[Quantity] AS Profit, (1-[sd].[Discount])*[sd].[Quantity]*[sd].[Price]*([s].[TaxPercent]/100) AS TaxAmount FROM Movie AS m INNER JOIN (Sale AS s INNER JOIN ((Product AS p INNER JOIN (MediaType AS mt INNER JOIN MovieProduct AS mp ON mt.MediaID = mp.MediaType) ON p.ProductID = mp.ProductID) INNER JOIN SaleDetails AS sd ON p.ProductID = sd.ProductID) ON s.SalesID = sd.SaleID) ON m.MovieID = mp.MovieID;
6. Select Form1 from the Properties drop down control and see the properties grid fill with all of the Forms' properties:
7. Click the Events button on the Properties toolbar and see the properties grid fill with all of the Forms' events:
8. Double click on the word Load in the properties grid and paste the following code inside the Form1_Load method:
rdDataSource1.Connect();
9. From the File menu, choose the Save All menu option.
Perform visual analysis against live data
To perform visual analysis against live data
- From the Debug menu, choose the Start Debugging menu item.
- Begin by setting the Marking to Shape.
- Drop Profit to the Rows shelf.
- Drop Sales Amount to the Columns shelf.
- Drop Quantity to the Size shelf.
- Expand the Attributes dimension.
- Drop the Media field to the Shape shelf.
- Drop MPAA to the Color shelf.
- Filter the MPAA field to only show PG, PG-13 and R.
- Click the MPAA drop down icon.
- Choose the Show quick filter menu option.
- Uncheck Approved, G and M members.
- Drop the Sale Date hierarchy to the Pages shelf.
- Click on the Cards toolbar button and check the Summary card.
- Select a range of data points.
- Now, just keep playing...