Intellisoft LLC BI/DW Services
Based on many years of experience in deep data migration and analysis, and the OLE DB providers developed by Intellisoft LLC, our company offers efficient, reliable and cost-saving analysis/design/development/support of Business Intelligence (BI) solutions of various complexity (self-service BI, data visualization, data warehousing, single version of the truth, etc.). The solutions are based on:
- Microsoft SQL Server 2008/2012/2014:
- OLTP (real-time transactional databases);
- OLAP (Data Warehouse/Data Mart historical databases);
- Microsoft SQL Server BI tools:
- SSIS (SQL Server Integration Services);
- SSRS (SQL Server Reporting Services);
- SSAS (SQL Server Analysis Services);
- IBM Cognos Framework/Reporting;
- Tableau reports;
- Various databases (PostgreSQL, Oracle, Sybase, Greenplum, Redshift, Apache Cassandra, Vertica, Denodo, and more).
Our New York and Phoenix, AZ -based analysts and developers will:
- analyze your existing databases and your data migration goals;
- design new database structure at your destination database server, either real-time transactional database (OLTP) or Data Warehouse database (OLAP);
- build ETL process as a set of SSIS packages to extract, massage and upload the data from original source into the new destination;
- troubleshoot and optimize all processes involved;
- deploy and automate the complete solution;
- provide 24/7 real-time support.
- review the existing reporting processes due to migrate (emails, Excel, PowerPoint, Crystal Reports, etc.);
- work closely with business users to create clear and straightforward business/technical reporting specifications for existing and new reports;
- using client-accepted reporting system develop samples/prototypes/templates of proposed reports and present them for user acceptance;
- develop completed reporting solution in constant close contact with business users to meet their expectations;
- deploy, automate and maintain complete reporting solution.
- based on existing data identify/analyze/extract the data essential for future data analysis;
- design Data Warehouse/Data Mart to store extracted data, if needed;
- design and develop SSAS Cube or IBM Cognos Framework to prepare data for data analysis;
- develop/deploy/maintain client-oriented interface to manipulate with the data:
- SSRS / IBM Cognos / Tableau reports
- Excel connection, PDF files, etc.
|SQL Server version of the demonstration package is available from the link. Please read the Instructions document for description of the project.|
SSIS (SQL Server Integration Services)
We have extensive knowledge and experience for designing Data Warehouse/Data Mart solutions of various scales and complexity. We are highly proficient in designing various reporting solutions using different reporting systems. If you have a multi-year historical data available, waiting to be analyzed and reported by a different criteria, the best tool to analyze it is SSAS Cube, where the data is pre-aggregated and optimized automatically. Using SSAS Cube will make your Excel Pivot tables or online summary reports run on amazing speeds.
1. Data Warehouse Sample Diagram
The ETL solution could be very simple, where data is read from a source database, one table or multiple tables, and loaded straight into the destination database, as is, no data massaging applies. The sample SSIS package for such solution resembles the screenshot below. This sort of ETL solution could be build using just 1 SSIS package.
2. Simple SSIS package
The ETL solution can be very complex, with multiple databases used as sources/destinations, located on different servers, and even a mix of different database providers (SQL Server, PostgreSQL, Oracle, Sybase, Greenplum, Redshift, Cassandra, Vertica, Denodo, MS Access, etc.). Additionally data sources could be client-provided or client-specified XML files, Excel files, comma/pipe/tab delimited flat files, custom-formatted flat files, encrypted files with available decryption solution/logic (.Net libraries, online applications, open source libraries). The ETL solution will consist of multiple SSIS packages, each one focusing on one particular data set or some supporting task. Sample SSIS package, performing multiple data sets reads and data massaging/matching/joining is presented on the screenshots below.
3. SSIS package Control Flow
4. SSIS package Data Flow
Separate solutions could be created that are completely focused on data quality. Very often collecting data over multiple years, especially based on user input and human interaction lead to some invalid data in the system, which is due to data complexity, data redundancy, time constrains, lack of resources, budget limit, etc. cannot be addressed in the current system. Loading data into a new database solution is the perfect time to cleanse the historical data, get rid of duplicates and invalid, incomplete, partial, or lost data. Intellisoft LLC itself uses custom SSIS solutions to test all new versions of its OLE DB drivers before releasing them to clients. The screenshot of a test SSIS package is below.
5. Multi-step SSIS package
Along with extensive use of native SSIS tools, in case of highly custom data massaging, database-stored stored procedures/functions/views, SQL queries and C# are used wherever needed.
SSAS (SQL Server Analysis Services)
SSAS Cubes are ideal solution to analyze high volume of historical, multi-year data. Based on various internal relationships the numeric data can be aggregated different way (sum, average, minimal value, maximum value, first/last value in the series, etc.) These aggregations are done along various time periods and also for multiple combinations of various data points. The aggregated values are stored then within SSAS Cube and could be queried using MDX (Multi-Dimensional eXpression) programming language. The SSAS Cube could be easily connected to Excel and various reporting system for text/graphical presentation. The underlying data structure inside the SSAS Cube looks similar to the screenshot below.
1. SSAS Cube Data Source View
2. SSAS Cube Query Designer View
SSRS (SQL Server Reporting Services) and Tableau
SSRS reporting solution is used to graphically represent data using variety of data sources. The perfect source for high-level summary reports/dashboards is SSAS Cube. The data can be represented using one or combination of several reporting tools.
- Row-based (table) and column-based (matrix) reports with multi-level grouping, interactive sorting, collapse/expend, group and grand totals, coloring based on criteria, jumping from summary report to detail report or to web page by using data link, titles/sub-titles/sub-sun-titles, calculated on-a-fly columns, sub-reports, etc.;
- List reports where all report elements are independent of each other;
- Graph reports (pie/line/bar/area) charts, performance indicators, gauges;
- Dashboards, where several different style business-related reports combined on one page;
- Scorecards, representing business performance in various areas.
1. Matrix report
2. Dashboard/scorecard reports
Intellisoft LLC stands behind its products 100%. We will closely work with you to:
- collect business and technical requirements;
- design the appropriate solution using various database objects, diagrams, BI tools;
- deploy completed solutions on client-owned servers, or Intellisoft-owned servers;
- provide technical documentation and user guide/how-to manuals;
- train the user's team if necessary;
- provide full technical support for developed solutions (support for 3-rd party-developed solutions is also possible);
- develop additional modification/extensions on clients request.
Our team is Microsoft Certified