Instant Microsoft SQL Server Analysis Services 2012 Dimensions and Cube [Instant] — Save 50%
Learn how to build dimensions and cubes in a SQL Server 2012 development environment with this book and ebook.
In this article by Anurag Acharya, author of Instant Microsoft SQL Server Analysis Services 2012 Dimensions and Cube, we will learn what SSAS 2012 is, why we need it, and then thread on a bit of description of the SSAS history. We will also discuss the architecture of SSAS 2012 with their data models, the future of SSAS 2012, along with what's new in SSAS 2012. After that we will talk about the SSAS dimensions and cube.
(For more resources related to this topic, see here.)
What is SSAS?
SQL Server Analysis Services is an online analytical processing tool that highly boosts the different types of SQL queries and calculations that are accepted in the business intelligence environment. It looks like a relation database, but it has differences. SSAS does not replace the requirement of relational databases, but if you combine the two, it would help to develop the business intelligence solutions.
Why do we need SSAS?
SSAS provide a very clear graphical interface for the end users to build queries. It is a kind of cache that we can use to speed up reporting. In most real scenarios where SSAS is used, there is a full copy of the data in the data warehouse. All reporting and analytic queries are run against SSAS rather than against the relational database. Today's modern relational databases include many features specifically aimed at BI reporting. SSAS are database services specifically designed for this type of workload, and in most cases it has achieved much better query performance.
SSAS 2012 architecture
In this article we will explain about the architecture of SSAS. The first and most important point to make about SSAS 2012 is that it is really two products in one package. It has had a few advancements relating to performance, scalability, and manageability. This new version of SSAS that closely resembles PowerPivot uses the tabular model. When installing SSAS, we must select either the tabular model or multidimensional model for installing an instance that runs inside the server; both data models are developed under the same code but sometimes both are treated separately. The concepts included in designing both data models are different, and we can't turn a tabular database into a multidimensional database, or vice versa without rebuilding everything from the start. The main point of view of the end users is that both data models do almost the same things and appear almost equally when used through a client tool such as Excel.
The tabular model
A concept of building a database using the tabular model is very similar to building it in a relational database. An instance of Analysis Services can hold many databases, and each database can be looked upon as a self-contained collection of objects and data relating to a single business solution. If we are writing reports or analyzing data and we find that we need to run queries on multiple databases, we probably have made a design mistake somewhere because everything we need should be contained within an individual database. Tabular models are designed by using SQL Server Data Tools (SSDT), and a data project in SSDT mapping onto a database in Analysis Services.
The multidimensional model
This data model is very similar to the tabular model. Data is managed in databases, and databases are designed in SSDT, which are in turn managed by using SQL Server Management Studio. The differences may become similar below the database level, where the multidimensional data model rather than relational concepts are accepted. In the multidimensional model, data is modeled as a series of cubes and dimensions and not tables.
The future of Analysis Services
We have two data models inside SSAS, along with two query and calculation languages; it is clearly not an ideal state of affairs. It means we have to select a data model to use at the start of our project, when we might not even know enough about our need to gauge which one is appropriate. It also means that anyone who decides to specialize in SSAS has to learn two technologies. Microsoft has very clearly said that the multidimensional model is not scrapped and that the tabular model is not its replacement. It is just like saying that the new advanced features for the multidimensional data model will be released in future versions of SSAS. The fact that the tabular and multidimensional data models share some of the same code suggests that some new features could easily be developed for both models simultaneously.
What's new in SSAS 2012?
As we know, there is no easy way of transferring a multidimensional data model into a tabular data model. We may have many tools in the market that claim to make this transition with a few mouse clicks, but such tools could only ever work for very simple multidimensional data models and would not save much development time. Therefore, if we already have a mature multidimensional implementation and the in-house skills to develop and maintain it, we may find the following improvements in SSAS 2012 useful.
Ease of use
If we are starting an SSAS 2012 project with no previous multidimensional or OLAP experience, it is very likely that we will find a tabular model much easier to learn than a multidimensional one. Not only are the concepts much easier to understand, especially if we are used to working with relational databases, but also the development process is much more straightforward and there are far fewer features to learn.
Compatibility with PowerPivot
The tabular data model and PowerPivot are the same in the way their models are designed. The user interfaces used are practically the same, as both the interfaces use DAX. PowerPivot models can be imported into SQL Server Data Tools to generate a tabular model, although the process does not work the other way around, and a tabular model cannot be converted to a PowerPivot model.
Processing performance characteristics
If we compare the processing performance of the multidimensional and tabular data models, that will become difficult. It may be slower to process a large table following the tabular data model than the equivalent measure group in a multidimensional one because a tabular data model can't process partitions in the same table at the same time, whereas a multidimensional model can process partitions in the same measure group at the same time.
What is SSAS dimension?
A database dimension is a collection of related objects; in other words, attributes; they provide the information about fact data in one or more cubes. Typical attributes in a product dimension are product name, product category, line, size, and price. Attributes can be organized into user-defined hierarchies that provide the paths to assist users when they browse through the data in a cube. By default these attributes are visible as attribute hierarchies, and they can be used to understand fact data in a cube.
What is SSAS cube?
A cube is a multidimensional structure that contains information for analytical purposes; the main constituents of a cube are dimensions and measures. Dimensions define the structure of a cube that you use to slice and dice over, and measures provide the aggregated numerical values of interest to the end user. As a logical structure, a cube allows a client application to retrieve values—of measures—as if they are contained in cells in the cube. The cells are defined for every possible summarized value. A cell, in the cube, is defined by the intersection of dimension members and contains the aggregated values of the measures at that specific intersection.
We talked about the special new features and services present, what you can do with them, and why they’re so great.
Resources for Article:
- Creating an Analysis Services Cube with Visual Studio 2008 - Part 1 [Article]
- Performing Common MDX-related Tasks [Article]
- How to Perform Iteration on Sets in MDX [Article]
About the Author :
Anurag Acharya is an information security researcher, a consultant, and an expert in networks and server security. He has eight years' practical experience with different vendor technologies, such as Cisco, HP, IBM, Avaya, Red Hat, Microsoft Servers, and network security software such as Panda Firewall. He has a keen interest in working with different technologies. He started his career as a hardware and network engineer, and later he worked as a corporate trainer. He trained hundreds of students, engineers, and government employees in Microsoft Servers (Exchange, Threat Management, Voice, and SQL). As his latest Network profile, he managed some datacenter production servers at a reputed company. He also has experience with Active Directory, DHCP, DNS servers, web servers, application servers, Exchange Servers, SharePoint Servers, database servers, and so on with their administration, monitoring, and security. He has a few international credentials including MCSE, MCSA Security, MCSA Exchange, MCITP Enterprise, MCSA on Core Server, CCNA, CCNA Security, CCNP, CCNP Security, CEH, and ACSE (IT security and ethical hacking). Currently, he is working as a freelance ethical hacking trainer and penetration tester with several institutes. He also provides private training online as well as offline. Soon, he will launch a website, www.mrintruder.com, through which he hopes to help students and professionals to explore their knowledge in different technologies.
He is also in the process of writing two books on Microsoft technologies: Windows Server 2012 and Exchange Server 2013. These books help the server administrators to manage, administrate, and upgrade servers on Windows Server 2012 and Exchange Server 2013.