Business Intelligence Tools
Many enterprise BI vendors, including Microsoft, now support a dizzying array of tools for business intelligence and data warehousing. The real trick is matching the correct tool with the business problem. We provide a brief description of some of the major components of the SQL Server 2017 stack below. For a more generic vendor neutral introduction to the purpose and use of each of the BI components see our articles on the Generic BI Solution Architecture, BI Technology Strategy, and BI User Tools.
SQL Server 2017 Components
SQL Server Database Engine
Build your data warehouse for large or small deployments. Table partitioning, compression, in-memory store, column store technology, and the MERGE statement are key features that assist with typical data warehouse challenges.
Integration Services (SSIS)
Extract data from your operational systems, spreadsheets, and external sources and load them into the data warehouse. Enhance data quality with fuzzy logic and data cleansing tools. Schedule and manage simple or complex ETL loads.
Reporting Services (SSRS)
Create standard parameterised reports or more complex dashboards with navigation, drilldowns, KPIs and indicators. Schedule reports for delivery by email or create data driven subscriptions.
Analysis Services Cubes (SSAS)
Create a data model that can hold billions or records whilst being fast and easy to query for end users and analysts. Analysis Services databases can be queried using Excel Pivot Tables, Performance Point, Reporting Services, and many more third parties BI tools including Strategy Companion Analyzer and Tableau.
Analysis Services Data Mining (SSAS)
Create predictive models for grouping customers, market basket analysis, trend prediction, and text mining. One of the most undervalued tools in the SQL Server BI stack is the data mining. It is easier to use than you might think and can answers questions that would be very difficult to address with traditional query based analysis.
Power BI
Power BI is best known to users as set of tools embedded in Excel and Power BI Desktop for modelling and reporting tool. Power BI allows users to build maintainable and automated solutions for financial modelling, reporting, dashboard, and data analysis requirements. Power BI was first introduced as a Power Pivot Excel Add-In for Excel 2010, and has been a core product feature since Excel 2013. But Power BI is more than a set of report authoring tools. It is a complete business intelligence and reporting platform with integration into Azure, Microsoft 365, and SQL Server product sets.
About CMBI
CMBI designs and develops business intelligence and analytics solutions for clients in Sydney and regional NSW. CMBI technology expertise includes Power BI, Power Pivot, Excel, SQL Server, Analysis Services, SharePoint BI, and Tableau.