Analysis Services is the cornerstone of your business intelligence infrastructure. If you already running SQL Server 2005 or greater, you have all the capabilities of building a data warehouse without having to purchase expensive data modeling software. SQL Server 2005 Analysis Services (SSAS) provides a unified and integrated view of all your business data as the foundation for all of your traditional reporting, online analytical processing (OLAP) analysis, Key Performance Indicator (KPI) scorecards, and data mining.
The data warehouse schema is configured in a concept known as “Star Schemas” The star schema is based on a central fact table with multiple dimensions and measurements. For example, get total sales by state, rep, month for the last two years. This query would hit a 3D data model (aka Star Schema or Data Cube). The presentation layer of the data can be delivered in an Excel format or deployed to SQL 2005/2008 Repoting Server. This is a dynamic and interactive report. When I say dynamic, Excel actually creates a “data” connection to the SQL Server Analysis Service. Excel Pivot tables and Dashboards can easily be created.
Screen Shot of a Star Schmea (click to zoom in):
Fact Table
Fact table A fact table contains the basic information that you wish to summarize. This might be order detail information, payroll records or anything else that's amenable to summing and averaging. Any table that you've used with a Sum or Avg function in a totals query is a good bet to be a fact table.
For this example, our fact table contains the following fields:
| Column |
Data Type |
Comments |
| CUSTEDP |
numeric |
|
| FULLORDERNO |
char(12) |
|
| COMPANY |
char(2) |
|
| DIVISION |
char(2) |
|
| SOURCE |
char(16) |
|
| OFFER |
char(8) |
|
| USE |
char(8) |
|
| MAILDATE |
datetime |
|
| ENTRYDATE |
datetime |
|
| ENTRYTIME |
datetime |
|
| PAYMETHOD |
char(2) |
|
| SHIPMETHOD |
char(2) |
|
| ITEMEDPNO |
numeric |
|
| QTY |
numeric |
|
| LINENUMBER |
numeric |
|
| EXTPRICE |
numeric |
|
| EXTCOST |
numeric |
|
| LINESTATS |
char(2) |
|
This table contains normalized Ecometry Data. I will post the SQL scripts shortly. If you would like them now, shot me an email ( brennan@plasmawebservices.com).
Measurements
Every cube will contain one or more measures, each based on a column in a fact table that you'd like to analyze. In the cube of book order information, for example, the measures would be things such as unit sales and profit.
The center table aka the “fact” contains all the measurements In this example, the measurements are extended cost and price.
Dimensions Table
A dimension table contains hierarchical data by which you'd like to summarize. Examples would be an Orders table, that you might group by year, month, week, and day of receipt, or a Books table that you might want to group by genre and title.
Dimensions
Each cube has one or more dimensions, each based on one or more dimension tables. A dimension represents a category for analyzing business data: time or category in the examples above. Typically, a dimension has a natural hierarchy so that lower results can be "rolled up" into higher results. For example, in a geographical level you might have city totals aggregated into state totals, or state totals into country totals.
For this example our data dimensions are:
Drill down on
Customers
company
name
state>
Zip
RFM
Orders
Line item status
Extended Cost/Price
Quantity
Line number
Pay Methods
Ship Methods
Items
Item Number
Item description
Item Category
Item Status
Sources
Source
Offer
Use
Campaign Statics
Screen Shots
Below are some sample screen shots to show you the power of the SQL Server 2005 Analysis Services. All of these reports were created dynamically through the Excel presentaion layer within a matter of minutes. No programming or SQL knowlege is needed. The reports are created in real-time by dragging and droping dimensions and measurements onto the Excel Pviot Table. The reports can also by deployed to a SQL 2005/2008 Web Reporting Server. Where you can actually schedule the reports as jobs and have them emailed to you in a number of formats.
If you are using Excel 2010, there is much more functionality using PowerPiviot Tables:
"PowerPivot for Excel is a data analysis tool that delivers unmatched computational power directly within the application users already know and love—Microsoft Excel. It’s the user-friendly way to perform data analysis using familiar Excel features you already know, such as the Office Fluent user interface, PivotTable and, PivotChart views, and slicers. It’s the fast way to generate rich and interactive analysis tools, easily mistaken for IT solutions after weeks of effort. It’s the right way to achieve deeper business insight and shorter decision cycles."
Click Here for more info
Screen Shot of Offer "on demand" sales by year and quater. Notice the "+" and "-". This is the drill down/up functionality. ( click to zoom in)
Screen shot of offer sales by state and item number. Filtered by division, year and source ( click to zoom in)
Screen shot of offer sales by order numer, item and item description. Filtered by division, year and source, month and ship method ( click to zoom in)
Screen shot of order status and sales. Filtered by division, year and source, month and ship method ( click to zoom in)
The next post will walk you through the process of creating your data cube. This will be step by step walkthrough on creating an Analysis Service Project in Visual Studios.
Let me know if you have any questions!
Subscribe