Database Reference
In-Depth Information
The answers to these questions allow you to better locate the data necessary for your solution. Data can be found
in many forms, and you may use one or more types to fill your requirements.
Some common data sources include the following:
•
Spreadsheets
•
Existing databases
•
Simple text files•
•
Log files•
•
XML text files•
•
Paper documents
Once the data is located, the next step is to decide how much of it is relevant to your needs. You also need to
decide whether your data's current location is sufficient for your BI solution's needs or whether you must copy
some or all of the data to a more appropriate location. This leads us to step 2.
Step 2: Plan the BI Solution
Few developers relish creating extensive documentation before building a project. And yet, just as it is necessary
for blueprints to be drawn up and approved before a home is built, projects must be planned and documented
before creating a working BI solution.
In Chapter 4 we discuss creating a description of what your solution will accomplish, documenting the
source and the destination objects, and beginning the formal documentation. A solution's formal document can
be laid out with common tools such as Microsoft Excel or even Microsoft Word. These Excel or Word documents
can then be taken back to the client for approval. Once approved, these documents will become an outline that
can be worked with much like a blueprint. You then create Visual Studio projects that become the building blocks
of your BI solution from these blueprints.
Step 3: Create a Data Warehouse
Your BI solution data will typically end up stored in a data warehouse database. Microsoft's SQL Server 2012
makes this very easy and cost efficient. Microsoft's SQL Server takes time and effort to master, yet the vast
majority of tasks required to build your solution are performed using tools that are as simple to use as Microsoft's
user-friendly Access database application.
In Chapters 4 and 5, we show how to design and implement a data warehouse database yourself, regardless
of your level of experience with Microsoft's SQL Server. Various design options are demonstrated in these
chapters, such as star versus snowflake dimensions and how to create fact and dimension tables. Once complete,
you will understand the design differences between online transaction processing (OLTP) and data warehouse
tables similar to those shown in Figure
1-2
.
