“The PCA crew is leagues above
the competition” - Pepsico
"a prior vendor failed to develop
the application, which (Practical) delivered." - Franklin Park
"the application (is) easier to use, with a
much simpler, more intuitive use interface" - Royal Administration Services
"Sometimes it's the things you don't ask for
that are important" - Royal Administration Services
"PCA turned our raw data and algorithms into easy to use
and understand features and reports." - CareScout
"The quality and professionalism ... has been
so outstanding that we want to send you a letter of thanks." - USALCO
"PCA people and applications stand out by
far exceeding our expectations." - USALCO
"The resulting application greatly increas(ed)...
our business effectiveness and efficiency." - The Advest Group
"PCA completes projects
efficiently and effectively" - Pepsico
Data Warehousing with SQL Server
Practical Computer Applications (PCA) engages a variety of technologies for data collection into data sourcing databases and then ETL: Extracts them into data staging databases, Transforms those data into a data warehouse and finally Loads all of it to a data mart.
In many cases, in order to reduce the complexity of the infrastructure of the project, we will combine the data warehouse and data mart into one data store and call it DW for short. In a traditional interpretation, the term Business Intelligence (BI) describes the process of extracting information from data. An integral part of any BI system is the data warehouse — a central repository of data that is regularly refreshed by the ETL processes we mentioned above.
SQL Server Integration Services – SSIS – is the most advanced technology for the ETL processes and we usually recommend for most of our projects. Typically the data warehouse (DW) is structured with a star schema.
After data is loaded/refreshed into the data warehouse, many BI systems usually reprocess subsets of the data into function-specific multi-dimensional OLAP cubes (typically implemented with SSAS – SQL Server Analysis Services) or other datasructures optimized for Data Visualization, Reporting Systems (typically implemented with SSRS – SQL Server Reporting Services) and Dashboards.
PCA always starts with Business Discovery and captures it in Analytical Requirements.
In a large enterprise, users tend to split into many groups, each with different analytical requirements. Users in each group often articulate the analysis they need in terms of graphs, grids of data (worksheets), and printed reports. These are visually very different but all essentially present numerical measures filtered and grouped by the members of one or more dimensions. PCA will capture the analytical requirements of a group simply by formalizing the measures and dimensions that they use.
As shown in picture above, these requirements can be presented graphically together with the relevant hierarchical information in so called “Sun model”, which is the logical model of the analytical requirements and capture the analytical requirements of users in terms of measures, dimensions, and hierarchical structure.
One of main reason to generate reports is to transform data into information for the business user and enable user to explore and drilldown data. Most people find graphics easier to understand than numbers. With SSAS and SSRS reports the dashboard can contain the gauges and/or KPIs (Goal, Value, Status, Trend, Weight, Indicator etc.), as a visual output. A gauge displays a single value from the data. As the picture below shows, gauges are particularly useful for an creating easy-to-read dashboard that displays and compares several values and monitor them for alerts, alarms and actions.
Recently Microsoft added to the SQL Server portfolio a Parallel Data Warehouse (PDW) with Massive Parallel Processing (MPP) Architecture, and ability to scale above 100s of Terabytes. PDW are appliances already available from HP and Dell.
Contact PCA to learn more about data warehousing and SQL Server for your application.
Most Popular Pages
Recent Posts: Practical Blog
- Power View empowered practical Smart Client applications
- Tableau vs Qlikview and Tableau vs Spotfire
- Data, Story, View: Prototype and Update
- 2012 starts with Tableau 7.0
- SQL Server 2012 is good for Data Visualization
- Spotfire 4.0 is available
- Introduction of QlikView 11
- VisiCalc jubilee and history of Excel
- Data Visualization 2011: Omniscope 2.6
- Data Visualization 2011: Spotfire 3.3, Silver 2.0
- Data Visualization 2011: Tableau 6.1
- SQL Server 2012 is released for Preview