Databases Reference
In-Depth Information
1 The SQL Server database engine
The SQL Server database engine is the core service for storing, processing, and securing data. It
serves as the engine for both the staging and the data warehouse databases. BI data is derived from
databases managed by the SQL Server database engine and other database engines. It is also derived
from NoSQL solutions, the Windows Azure Marketplace, and other sources. The engine hosts the data
warehouse and transactional databases and is the underlying data in Analysis Services (multidimen-
sional and tabular). We explain these later in this chapter.
2 SQL Server Integration Services or other tools
Microsoft SQL Server Integration Services (SSIS) is one tool that gives you data integration and data
transformation solutions to help you get to trusted data faster. You can create repeatable data inte-
gration by using the ETL process. This process can be automated, moving data from sources such as
XML data files, flat files, or relational data sources to one or more destinations. Data from disparate
sources must often be cleansed.
Features that make SSIS compelling for ETL solution building include:
A user interface that helps users move data and perform ETL.
■
Wizards for importing and exporting data as well as those that help create a package contain-
ing the business logic to handle data extraction, manipulation, and transformation. Workflow
elements help process data.
■
Runtime debugging so that BI developers can step into code and watch the processes in
a package. Reusable event handlers also help developers to create rich, repeatable ETL
processes.
■
On another note, because this topic is about SharePoint, it makes sense to mention that Share-
Point is also a source system from which to extract and load data. It is becoming increasingly more
important to know how to move data from and to SharePoint as companies adopt it to store business
data. Some data will be tied directly to another software product that has partnered with SharePoint
for sharing. Often, the data from a SharePoint list can be exported in the form of an Excel file or an
Open Data Protocol (OData) feed and then pulled into a tabular model in either Excel, PowerPivot, or
the SQL Server Data Tools.
More Info
To learn more, read “SQL Server Integration Services,” which is available at
Big Data is sometimes defined as there being so much data that efforts to get value from it needs
to be spread across multiple workloads. Big Data as an unstructured data source will become increas-
ingly important. We have created a separate section in Appendix B, “Microsoft and “Big Data,” to
inform you as to what is here and coming for Big Data, SQL Server integration, and how it will affect
SharePoint.
Search WWH ::
Custom Search