Microsoft Technologies for Business Intelligence MSBI
Understanding the terminology that surrounds BI is important as you embark upon your BI project. The table below outlines key terms that are used in the BI arena which are as follows
- SSIS – SQL Server Integration Services
- SSAS – SQL Server Analysis Services
- SSRS- SQL Server Reporting Services
- Microsoft Office SharePoint
- Microsoft Excel
SSIS – SQL Server Integration Services
- SSIS is an ETL tool that is responsible for moving data around the business and transforming or changing the data.
- There are a wide range of transformations that can be performed by SSIS. These can involve changing the format of the data or converting data from one data type to another.
- You can also perform lookup operation to add data to a destination based on a lookup from a third data source.
- For example, based on postal code, look for suburbs from the U.S. Postal Service.
- While SSIS can be used to populate a data warehouse, it can also be used to perform ad hoc data transfers between different systems.
- It can also be used to move objects between different instances of SQL Server. The first interaction that people have with SSIS is when they use the Import/Export wizard.
- Not only will this provide the ability to define data sources and destination, but you can also perform transformations within the wizard.
- The wizard also prompts you to save the detail of the wizard as an SSIS package that can be edited later within SSIS.
SSAS – SQL Server Analysis Services
- SSAS provides OLAP databases and data mining capabilities. As the need for data increases for business analysis within an organization, many BI IT professionals are seeing the value that SSAS can bring to providing data analysis in an efficient manner within a BI infrastructure.
- It also provides the ability to add calculated values to a cube known as calculated members. This use Multidimension Expression (MDX) language to create calculated members that will add value to the cube.
- MDX can also be used to define KPIs that provide high visualisation of key business metrics. Another component of SSAS is data mining.
- This is perhaps one of the most underused technologies within the entire SQL Server BI stack.
- The power to find patterns and trends in the data can help companies make commercial decisions about the future.
- Excel 2007 now has a data mining add-in that can extend the reach of this technology to the end user.
- Understanding how data mining works will be an important aspect of Analysis Services as it grows in popularity.
SSRS- SQL Server Reporting Services
- SSRS provides you with the capabilities to design, deploy, manage and deliver end-user reports. The design capabilities is available to all users of the organisation as you can use Business Intelligence Development Studio to create reports.
- Report Designer can be made available to the end user to create their own reports.
- Report Manager is a Web-based front-end that enables users to view reports and allow report administrators to manage reports. You can also automate the delivery of reports.
Microsoft Office SharePoint
- Although Microsoft Office SharePoint Services (MOSS) is not a part of the SQL Server BI stack, it would be remiss not to acknowledge the growing importance that MOSS has in delivering BI to the end user.
- MOSS can act as a central repository of business information within an organization. This can include the storage of documents and images.
- You can also set up calendars and newsgroups that an organization can use to exchange information.
- You can now integrate SSRS with MOSS during the installation of Reporting Services. This will enable you to host and store reports within MOSS.
- Furthermore, it was announced by Microsoft in January 2009, that Microsoft Office Performance Point Server would be integrated into SharePoint Services.
- This would provide greater capabilities for providing digital dashboards and scorecards within MOSS.
- Microsoft Excel is a popular client tool for BI solutions. You can export SSRS reports to Excel. However, its power is evident in the way that it integrates with SSAS.
- You can connect to SSAS cubes by using Excel and create pivot table reports.
- Furthermore, you can also download a number of Excel add-ins such as the Analysis Services Add-in for Excel and the Data Mining Add-in for Excel.
- These add-ins provide more sophisticated integration with Analysis Services than the pivot table alone.