Database Design, Integration and Data Cleaning Services for Data Warehouse and Analytical Cubes

Explanation of services will be aimed at users new to using Databases Design & Development Services for their business and hopefully, users familiar with these types of services will still find it useful in understanding the Services offered by Panasoft.

Database Contracting Services generally apply to 2 of the 4 operational areas when using Database Technology in a commercial business environment. The 2 that are not so applicable to consultancy are generally carried out in house by the company's staff where the daily Database Administration is performed by the DBA and business processes like Customer Relationship Management (CRM), Enterprise Resource Planning (ERP) and Sales Order Processing (SOP) That make up the normal day to day running of the business by the companies IT users with Web and Windows Applications to process data on the corporate database.

The remaining 2 operational areas which are more suitable for consultancy services are centred around the initial design and development of the database and the development of a data environment suitable for high speed and on-demand analytics and reporting. These are often one-off projects that may benefit from expertise in the areas of database design and development being the types of services offered by Panasoft.

The Database Design & Development Services offered by Panasoft can be considered as projects of work to achieve a goal for your business. Shown below is a typical chronological list of projects of work associated with Business Databases in a commercial environment and any one of these projects of work can be undertaken in isolation to suit the individual business needs of your company.

​

Operational Area One

Database Design & inflows

​

The majority of Database Modelling is carried out at the initial design stage for the needs of a business database, but modelling can be useful when changes are made to keep up with new requirements and to further optimise the overall database structure to be more robust and efficient. 

​

Database Modelling Service may constitute the following :-

​

• Analysis of the business needs by consultation to design data structure

• Evaluate existing IT infrastructure for database capacity & performance

• Advise on Version & Licences of Microsoft SQL Server Database

• Create and Deliver Full Data Relational Diagrams & Data Dictionary

• Install & Setup SQL Server on Local Windows Server or Microsoft Azure Cloud  

• Build Database with Keys, Relations & Referential Integrity

• Test database on Sample Real Data for Consistency & Concurrency

• Document all Processes and Add to Master Project Document for Client

​

 

Data Population for new databases is generally carried out by Bulk Data Transfer and is a one off process with minor tweaks to ensure data integrity is maintained after the data has been imported.

 

ETL (Extract Transform & Load) using SQL Server SSIS Data Integration tool can also be used for initial data population where the data imported is more complex.

​

Data Population Service may constitute the following :-

​

• Checking of all Raw Data to be Imported for Structure and Consistency

• Scanning of Paper Documents with OCR for Importing into Database

• Transfer of Data from Legacy Software Applications (Exported or Extracted)

• Transfer of Data from Spreadsheets, Excel Documents

• Transfer of Data from Text Documents and Comma Delimited Files

• Creation of T-SQL Code for Bulk Data Transfers & ETL SSIS Packages

• Clean, De-Duplicate & Validate Staging Area Data Prior to Final Transfer 

• Perform all Data Transfers and Validate Data Moved to SQL Database

• Document all Processes and Add to Master Project Document for Client

​

Data Integration Services can be a one off process or scheduled for repeat-ability and is generally applicable to an existing database requiring data from a 3rd party, like Pricing Information, Financial Index, Insurance Rating Tables and the like. The list of possible data to import into a production database can be extremely varied with the formats of the data being non-homogeneous. This is where Data Integration Services can come to the rescue.

​

Data Integration Service may constitute the following :-

​

• Evaluate all Data Input Streams for ETL Process for Structure and Concurrency

• Create ETL SSIS Packages for Data Transfer to Staging Area

• Clean, De-Duplicate & Validate Staging Area Data Prior to Final Data Transfer

• Create Final SSIS Packages with T-SQL & C#.net Code for Staging Data Transfer 

• Schedule SSIS Packages for Execution. Test Schedule & Data

• Document all Processes and Add to Master Project Document for Client

​

Operational Area Four

Data Analytics

​

The benefit of data analytics over different companies and business sectors is vast and extremely varied and each business gleans the benefits in different ways. This is why the final results from the data analysis process are often controlled and managed by the companies own staff who will know best how to benefit from this analysed data in their companies database.

​

Panasoft Data Analytic Services provide the expertise to extract and correlate the data to a format that the business can use for KPI Reports, Dashboards and the like.

​

Data analytics is a work backwards process from the business knowing it needs more analytic information from its corporate database, but not knowing how to extract it or how to get it promptly.

​

A wide range of data sources has been often the starting point of real-world data analytics in that a business will often have data not just in their corporate database but also in user's spreadsheets, other software application and data from 3rd parties that are not imported into the main database. Extracting meaningful data from these multiple data sources can be difficult to impossible without some professional data processing

​

A Data Lake is a data repository of your business data from multiple data sources and formats all stored in one place. It is possible to go from the multiple data sources straight to a Data Warehouse for Business Intelligence purposes using SSIS Data Integration Software packages, but having a Data Lake stage in-between the Data Warehouse allows for deeper investigation into all your business data prior building a Data Warehouse and in fact will often make your Data Warehouse a better resource for all your Business Intelligence KPI's, Reports & Dashboards.

​

A Data Warehouse is also a repository of your business data, but the data is Homogeneous and ready to provide KPI's, Reports & Dashboards from Business Intelligence tools like Microsoft Power BI or similar Business Intelligence software.

​

Panasoft can design, develop & build your Data Warehouse using Microsoft's SQL Server Integration Services SSIS and can be built from the raw data or from a Data Lake previously developed. A Data Warehouse is specifically designed for optimal data reporting by de-normalising your business data into a set of related data for rapid retrieval of business information that can benefit the company by providing previously unknown valuable Sales & Marketing information. 

​

If however you data is very large or the data retrieval is too slow, then you may need to implement the technology of Data Cubes.

​

A Data Cube can be built from your Data Warehouse where the data is further optimised for rapid data retrieval and is the logical step forward in Data Analytics where performance is of the utmost importance. This speed is brought about by the pre-aggregation of measurable statistical data and the use of optimising data that is date orientated by a form of date matrix.

​

These OLAP Data Cubes are developed using Microsoft SQL Server Analyst Services SSAS data tools and can build from a Data Warehouse or directly from your business OLTP database as required. 

​

In general, though a Data Cube is only really necessary with extremely large data sets as data retrieval speed can often be obtained by intelligent query design and SQL Server performance optimisation from a Data Warehouse and also directly from your normal business database.

​

If you have any questions about the services that Panasoft provide or would like to know more about how to extract valuable Sales & Marketing information from your business database, then please get in touch.