- About Quantrix
- Solutions
- Products
- Services
- Sample Models
- Learning
- Learning Overview
- Video Demos
- Webinar Series
- Quick Guide
- User Guides
- Modeler Notes
- Mortgage Modification Modeler Note
- Election Tax Model
- Marketing Response Curve Model
- Pivoting Data
- Quantrix in Corporate Budgeting
- Using Expressions in Quantrix
- Performing a Sales Analysis
- Indirect() w/Capex & Depreciation
- Analyzing ARM vs. Fixed Mortgages
- Integrated Financials Models
- Leveraging the @ and # Operators
- Using Circular References
- Converting Spreadsheets to Quantrix
- Using Functions with Black-Scholes
- Using Statistical Functions
- Using Quantrix in Capital Budgeting
- Working with Scenario Categories
- Whitepapers
- FAQ
- Support
- Scripting
- Store
Using a Database to Perform a Sales Analysis
DataLink allows modelers to connect to a wide variety of databases via JDBC / ODBC and on-line data sources through XML and Web Services. With this Modeler note, you will use the Quantrix DataLink functionality to connect to a Microsoft Access® database file and quickly turn the database information into a multidimensional sales analysis.
The Access database contains sales figures for a fictitious car manufacturer. Unit Sales are segmented by Model Class, Sales Channel, Engine Size and Quarter. To follow along with this modeler note, you will need to download an Access database file . Since Access in not available on OS X, users of Quantrix for OS X can follow along by using this .csv file. Just choose 'Delimited Text File' as the data source option in step 1. Step 2 will not be applicable. Quantrix for OS X can connect to any data source that utilizes a JDBC database connector.
| STEP 1 – CONNECTING TO THE ACCESS DATABASE
After downloading the access file, start Quantrix and choose 'Tools > DataLink > Create DataLink' from the menu. Choose the ‘JDBC / ODBC Data Source’ option from the first panel and click 'Next'. On the second panel, choose the Driver of ‘ODBC Database Connection’, URL type of ‘Microsoft Access File’ and click the ‘Browse’ button to locate the Access file you just downloaded. Click ‘TestConnection’ and verify if Quantrix can find and connect to the database. When done, click ‘Next’.
|
 |
| STEP 2 – INDENTIFYING THE DATA TO IMPORT
In the columns tab, click the plus sign next to the Auto_Sales table to expose the columns. Double click each column to make it part of the import process. When done, click on the SQL tab to inspect the SQL automatically built. When ready, click ‘Next’ to continue.
|
 |
| STEP 3 – CLASSIFYING THE DATA IN QUANTRIX
After reviewing the data preview screen, choose to import the data as a ‘Multi-dimensional OLAP analysis' into a New Matrix. On the screen assign each column to a data type and choose a category for MODEL_CLASS, SALES_CHANNEL, ENGINE and QUARTER. Leave UNITS_SOLD as an item. For the purposes of this exercise, you can click ‘Finish’ at this point.
|
 |
| STEP 4 – MANIPULATING THE DATA IN QUANTRIX
At this point, a summary analysis would be beneficial in the model. Right click on each of the categories and choose 'Insert Summary > Sum' from the menu. Note that a new item and summary formula are created to calculate the sums for each dimension. To get a percentage analysis by Quarter, add a new item by clicking on the UNITS_SOLD item and pressing 'Enter'. Rename the item to ‘Percentage’ and write the following formula:
Percentage = UNITS_SOLD/Sum of QUARTER:UNITS_SOLD At the end of each of the summary formulas, insert a ‘Skip Percentage’ statement to ensure that the percentages work correctly for each dimension |
 |
Quite quickly, you have been able to construct a multidimensional Quantrix model from a data source as well as add items and formulas to conduct further analysis. With the DataLink established, new information can easily be updated in the model. DataLink technology allows Quantrix to connect to any data source that utilizes an ODBC or JDBC driver.
Downloads:
>> Access database file
>> For OSX users only - .csv file
>> Auto Sales Model
