About me
Name: Nikolay Voronkov
Place: Tver, Russia
Email: voronkov@engineer.com
Skype: nik-voronkov
Job: Freelancer
My portfolio (by category)
MS SQL / Oracle / mySQL
ASP.NET / Silverlight
C# / PHP
Adobe InDesign (client/server)
Multilanguage support for Wordpress

The project DV-6

Customer: National Industrial Information Agency
Project type: Commercial Internet project
My position in project:
  • The database architect
  • The architect of software solutions
  • Programmer
  • Operation systems: Windows 2012
    Databases: MS SQL, MS Analisys Services (OLAP cubes)
    Languages: C#, SQL, MDX, XML, HTML, JavaScript
    Tecnologies: ASP.NET, Devexpress ASP.NET components, jQuery
    Interface languages: English, Русский
    IDE: MS Visual Studio 2010, MS SQL Server Management Studio

    The customer of the project has been collecting information on various areas: sales of cars, trucks, buses, trailers; the analysis of the dynamics of macroeconomic indicators, etc., the data is stored in usual databases, but for this project they are merging into a single repository in MS Analisis Services (OLAP cubes). The aim of the project is to provide access to data via the Internet using the browser without losing functionality, which can provide MS Excel.

    Before beginning work on the project I had analyzed the market of software components and technologies that could in a convenient way to show the contents of the OLAP cube, as well as on the basis of these data charts. I have considered the following products:

  • Saiku
  • ASP.NET – Developer Express
  • Telerik
  • Radar Soft

    From this set only the components from Developer Express was able to meet the high customer requirements of the project.

    The architecture of the project.

  • The MS SQL database keeps the basic system objects: users data, open sessions, information about the structure of the OLAP-cube, information about sales databases, components configuration (types of charts, description of hierarchies of different treeviews), dictionaries of messages and menu items in two languages, etc.
  • The project can connect to only one OLAP cube, which is regularly updated (upload new version and switch to that software using the configuration file).
  • The work of the browser interface provides a project ASP.NET using components from the company Devexpress.

    The main part of the project is the management block of the OLAP cube.

    The OLAP cube has of several hundred fields, so they are divided into logical blocks, the structure of these blocks are described using XML and stored in the project database. The user may not have access to all of the blocks, but only to part. If you have access to multiple blocks, there is a Union of fields from which these blocks belong. When requesting data, the user specifies the desired fields to the cube and built a dynamic query in the MDX language.

    Table management of a cube includes:

  • The selection tables part (block) for chart building.
  • Export the entire table or a selection in MS Excel.
  • The movement fields (dimensions) in the row or column area.
  • Move the block of data (measures) in columns or rows.
  • Set filters for selected fields.
  • Sorting data by field.
  • The expanding and collapsing of the table at different levels.
  • Building complex queries based on the filter management window.
  • The building of five types calculated fields.
  • The display of the part fields ([Other] field option).
  • Saving and restoring tables.

    Chart building

    Software from Devexpress has WebChart component for chart building. Charts can be built programmatically and using connection components PivotGrid and GridView that contain data in tabular form. In the second case, the plotting is trivial, as it does not require construction of chart series and populate them with values, but for this project, this approach proved to be unacceptable because the charts it was necessary to build on a selected block of data in the table, for technology ASP.NET component AspxPivotGrid does not have a built-in feature selection.

    The chart building in the project has the following features

  • Supports about 20 kinds of graphs
  • A chart can have any number of series
  • When plotting rows and columns can be interchanged
  • The chart can be rotated
  • For the nodes of the graph can be displayed in various arguments, this activates a tooltip with a detailed description of the node on which stands the mouse pointer
  • You can control the position of chart legend
  • For pie charts you can display data in percent
  • It is possible to build additional graphics with the second axis
  • The chart can be saved in file (PNG format).

    Color analize

    Cube table has the additional possibility for analysis – colour analysis, the concept of which was developed by project customer. Analyzes all data table, rows and columns with totals are excluded. If you enable data filtering on the number ([Other cell]), it is possible to leave or remove the entry [Other cells] from the analysis. If the table has multiple fields with data that you can use only one by choice, the field data must be in columns of the table. Each row is assigned a specific color, the set of possible colors is stored in the system registry.

    The analysis is sorted in descending order within the column, if there are multiple header fields, the grouping is to sort is by the last field.
    The results of the analysis can be exported to MS Excel.

    Search by VIN

    VIN – Vehicle Identification Number. The project customer has databases on sales of vehicles, the bases are divided by years and types of vehicles. This part of the project allows you to search for the code set of VINs in different databases. The search results are placed in the table, the possible group tables for any set of fields. After search you can see the codes that were not found in selected databases. The results can be exported to MS Excel.

    How it works.

    Data on sales of vehicles stored in the database tables MS SQL. The tables is divided by years and types of vehicles. Tables can have different structures depending on the type of vehicle with a set of common fields. In the system registry exists the list of available tables and their structure. When you query data from multiple tables using dynamic SQL is to combine information into one temporary table, which contains the full set of fields from all the tables. To avoid errors when you build the temporary table, the system tables of MS SQL database check to remove the fields that may be incorrectly specified for the analyzed tables.

    User management

    The project has following features for user management:

  • Display a list of users based on filters
  • The addition, correction and deletion of user
  • Change user password
  • The assignment of the user roles that allow access to various parts of the project
  • Assigning user rights to access the cube databases and databases for search by VIN
  • User authorization via SMS
  • Specify the starting and ending dates for the user account
  • Settings the user restrictions for access based on cube data (geography, dates, specifications, etc.)

    For access to project is using sessions. The user can open only one session, during the session, in one window of a browser it can open several tabs with data, data about the current session is stored in the project database. In the absence of user activity within a certain time of its session closes, for access to the project the user must login again.
  • Leave a reply:

    Укажите 3 цифры