NetConnect Blog - Your Resource For IT Tips, Tricks and News

Building the CUBE - Analyzing Business Data with Microsoft SQL Server Analysis Services

Posted by Joe Dunnigan on Nov 21, 2014 11:20:08 AM

ssasIt is common today for companies to gather data from many sources and store that data for long periods of time. But how can you tap into that data and utilize it to improve your business processes and product offerings? Today we'll look at one technology stack that helps you accomplish this goal: Microsoft SQL Server Analysis Services.

Your company data can come from many different sources. You may have accounting packages like Microsoft Great Plains, analytics data from web sources, spreadsheets and Access databases managed by your team, or even CSV text files with information from third parties. Sifting through all this information and making use of it can be cumbersome. So how do you make use of it?

Often times various data sources will have relationships that can be used to tie the data together. Perhaps you have sales figures or invoicing, and would like to tie these to trends in your e-commerce operations. Or you may find that you have years of sales data, and would like to quickly view trends and year-over-year comparisons. By taking your data sources and combining them into a single 'data warehouse' with relationships between the data defined, you can start building a reporting solution that will give you that extra insight into your business.


Using technologies in the Microsoft SQL Server family of products, we can create processes that will refresh and combine these various data sources on a regular basis and load them into a single location. In order to provide easy to use reporting, we can then connect to this data source using SQL Server Analysis Services. Analysis Services allows us to create an OLAP Cube - a structured set of data and queries that allow data to be quickly and efficiently reported on.

Once you've setup a data warehouse and an OLAP Cube, you need to connect to this source so you can start analyzing and reporting on the data. This can be accomplished easily with Microsoft Excel. From a blank Excel worksheet, you can define a data source and create a pivot table with your data in just a few clicks. Once connected, you can pull in columns and rows of data and start manipulating filters to get the views you're looking for. Using 'measures' and 'dimensions' provided by the Cube, you can sort and view data by such metrics as fiscal year, month and day, ordered vs. invoiced product, demographics, and any other relationships your data has.

After you've had some time to setup reports, you'll want to share them with team members. Excel Workbooks you create can easily be sent off to colleagues or placed on a network share for access by your peers. Setting up multiple sheets or workbook tabs with multiple views, charts and pivot tables can all be accomplished here.

If you have lots of data and would like to see if that data can help you gain insight into your business, Analysis Services is a tool worth considering. With a small investment, you may be able to find some great improvements for your business.  To build your cube, contact NetWork Center, Inc. today and schedule a data review consultation with one of our highly qualified database experts.

Contact Us Today!

Topics: Microsoft SQL, Analysis Services, Microsoft SQL Server Analysis Services

Subscribe to Email Updates

Recent Posts

Posts by Topic

see all