Creating an Analytical Solution in Microsoft SQL Server Analysis Services
Lua error in Module:Wikidata on line 170: attempt to index field "wikibase" (a nil value).
Lua error in Module:Wikidata on line 170: attempt to index field "wikibase" (a nil value).
Lua error in Module:Wikidata on line 170: attempt to index field "wikibase" (a nil value).
Lua error in Module:Wikidata on line 170: attempt to index field "wikibase" (a nil value).
Lua error in Module:Wikidata on line 170: attempt to index field "wikibase" (a nil value).
Lua error in Module:Wikidata on line 170: attempt to index field "wikibase" (a nil value).
Lua error in Module:Wikidata on line 170: attempt to index field "wikibase" (a nil value).
Lua error in Module:Wikidata on line 170: attempt to index field "wikibase" (a nil value).
Lua error in Module:Wikidata on line 170: attempt to index field "wikibase" (a nil value).
Microsoft Analysis Services(Analysis Services from Microsoft) is part of Microsoft SQL Server, a database management system (DBMS). Microsoft has included a set of services in SQL Server related to business analysis and data storage. These services include Integration Services and Analysis Services. Analysis Services, in turn, includes a set of tools for working with OLAP and data mining.
Story
Chronology
The following are the major events in the history of Microsoft Analysis Services, covering the period since 1996.
| date | Event |
|---|---|
| 1996-07-01 | Microsoft is recruiting a new team of developers to create an OLAP product codenamed Plato (rearranging the letters of the OLAP acronym) |
| 1996-07-15 | Panorama Software delegation meets with Microsoft representatives |
| 1996-10-27 | Microsoft announces acquisition of Panorama Software development team |
| 1998-11 | OLAP Services 7.0 is released (codename Sphinx) |
| 2000-08 | Analysis Services 2000 is released (codenamed Shiloh) |
| 2001-11 | XML for Analysis SDK 1.0 released |
| 2003-04 | ADOMD.NET and XML for Analysis SDK 1.1 is released |
| 2005-10-28 | Analysis Services 2005 (codenamed Yukon) is released |
| 2008-08-06 | Analysis Services 2008 is released (codenamed Katmai) |
Storage Modes
Microsoft Analysis Services is neutral in the MOLAP vs. ROLAP debate surrounding OLAP products. Thanks to this, you can use all types of MOLAP, ROLAP and HOLAP within one model.
Separate storage modes
- MOLAP - Multidimensional OLAP ( multidimensional OLAP) - The data itself and its processing are processed, stored and indexed using a special format optimized for multidimensional data.
- ROLAP - Relational OLAP - Data and its processing remain in the relational data source, eliminating the need for special processing.
- HOLAP - Hybrid OLAP ( hybrid OLAP) - this mode uses a relational data source to store the data itself, but preprocessing and indexes are stored in special format, optimized for multidimensional data.
Dimensional storage modes
- MOLAP - dimension and hierarchy attributes are processed and stored in a special format
- ROLAP - dimension attributes are not processed and remain in the relational data source. Partitions measured by the ROLAP dimension must also be in ROLAP mode.
Programming interfaces and object models
Microsoft Analysis Services supports various sets software interfaces(API) and object models for various operations in various software environments.
Data extraction
- XML for Analysis - API lowest level. Can be used on any platform and with any programming language that supports HTTP and XML
- OLE DB for OLAP - OLEDB extension. Based on COM and intended for use in C/C++ programs on the Windows platform.
- ADOMD - ADO extension. Based on the COM Windows platform.
- ADOMD.NET - ADO.NET extension. Based on .NET managed code on CLR platforms.
Administration and management
- DSO - For AS 2000. Based on COM Automation and intended for programs on the Windows platform.
- AMO - For AS 2005. Based on .NET technology and intended for programs written using managed code on CLR platforms.
Query languages
Microsoft Analysis Services supports the following query languages:
Data Definition Language (DDL)
DDL (Data Definition Language) in Analysis Services is based on XML and supports commands such as
Data Manipulation Language (DML)
- MDX - for querying OLAP cubes of a multidimensional model
- DAX - for querying OLAP cubes tabular model (D ata A nalysis e X pressures, PowerPivot)
- SQL - a limited subset of SQL statements for querying OLAP cubes and treating dimensions as tables
- DMX - for requesting models predictive analysis data
Write a review about the article "Microsoft Analysis Services"
Notes
Literature
- Sivakumar Harinath, Matt Carroll and others. Microsoft SQL Server Analysis Services 2008 and MDX for Professionals = Professional Microsoft SQL Server Analysis Services 2008 with MDX. - M.: “Dialectics”, 2010. - P. 1072. - ISBN 978-5-8459-1636-5.
- Sivakumar Harinath, Stephen Quinn: Professional SQL Server Analysis Services 2005 with MDX. ISBN 0-7645-7918-5
- Teo Lachev: Applied Microsoft Analysis Services 2005: And Microsoft Business Intelligence Platform. ISBN 0-9766353-0-5
- Reed Jacobson: Microsoft(r) SQL Server(tm) 2000 Analysis Services Step by Step. ISBN 0-7356-0904-7
- Claude Seidman: Data Mining with Microsoft SQL Server 2000 Technical Reference ."" ISBN 0-7356-1271-4
- George Spofford: MDX-Solutions. Wiley, 2001, ISBN 0-471-40046-7
- Mosha Pasumansky, Mark Whitehorn, Rob Zare: Fast Track to MDX. ISBN 1-84628-174-1
- ZhaoHui Tang, Jamie MacLennan: Data Mining with SQL Server 2005. ISBN 0-471-46261-6
- Edward Melomed, Irina Gorbach, Alexander Berger, Py Bateman: Microsoft SQL Server 2005 Analysis Services. ISBN 0-672-32782-1
Links
- (English)
- (English)
- (English)
- (English)
- (English)
- (English)
|
||||||||||||||||||||||||||||||||||||||||
Excerpt describing Microsoft Analysis Services
They stopped in the small town of Alytus (so that they would not have to pay too much for housing, since, unfortunately, it was quite difficult for them financially at that time). And so, while they were “looking around,” they didn’t even feel how completely enchanted they were by the beauty of nature, the comfort of a small town and the warmth of the people, which in itself seemed to invite them to stay at least for a while.And also, despite the fact that at that time Lithuania was already under the heel of the “brown plague”, it still somehow retained its independent and militant spirit, which even the most ardent servants of communism did not manage to knock out of it... And this attracted the Seryogins even more than the beauty of the local nature or the hospitality of the people. So they decided to stay “for a while”... what happened was forever... It was already 1942. And the Seryogins watched with regret as the “brown” octopus of National Socialism tightened its tentacles around the country they loved so much... Having crossed the front line, they hoped that from Lithuania they would be able to get to France. But even with the “brown plague,” the door to the “big world” for the Seryogins (and, naturally, for my dad) turned out to be closed, and this time forever... But life went on... And the Seryogins began to gradually settle down in their new place of residence. They had to look for work again in order to have some means of subsistence. But it turned out to be not so difficult to do this - there was always a place for those who wanted to work in hardworking Lithuania. Therefore, very soon life flowed along its usual course and it seemed that everything was calm and good again...
My dad began to “temporarily” go to a Russian school (Russian and Polish schools were not uncommon in Lithuania), which he really liked and he categorically did not want to leave it, because constant wandering and changing schools affected his studies and, more importantly, – did not allow me to make real friends, without whom it was very difficult for any normal boy to exist. My grandfather found a good job and had the opportunity on weekends to at least somehow “unwind” in his beloved surrounding forest.
And my grandmother at that time had her little newborn son in her arms and dreamed of not moving anywhere for at least a short time, because physically she did not feel too well and was, like her whole family, tired of constant wanderings. Several years passed unnoticed. The war was long over, and life was becoming more normal in all respects. My dad studied perfectly all the time and the teachers denigrated his gold medal (which he received after graduating from the same school).
My grandmother calmly raised her little son, and my grandfather finally found his long-standing dream - the opportunity to “plunge headlong into” the Alytu forest that he loved so much every day.
Thus, everyone was more or less happy and so far no one wanted to leave this truly “God’s corner” and again set out to wander along the main roads. They decided to give dad the opportunity to finish the school he loved so much, and to give his grandmother’s little son Valery the opportunity to grow up as much as possible, so that it would be easier to embark on a long journey.
But the days flew by imperceptibly, months passed, being replaced by years, and the Seryogins still lived in the same place, as if having forgotten about all their promises, which, of course, was not true, but simply helped them get used to the idea that they might not will be able to fulfill the word given to Princess Elena never again... All the Siberian horrors were far behind, life had become everyday familiar, and it sometimes seemed to the Seryogins that this was possible and had never happened, as if it had happened in some long-forgotten, nightmare dream. ..
Vasily grew and matured, becoming a handsome young man, and it increasingly seemed to his adoptive mother that he was her own son, since she truly loved him very much and, as they say, doted on him. My dad called her mother, since he still (according to the general agreement) did not know the truth about his birth, and in return he loved her as much as he would have loved his real mother. This also applied to his grandfather, whom he called his father, and also sincerely, with all his heart, loved.
So everything seemed to be getting better little by little and only occasional conversations about distant France became less and less frequent, until one fine day they stopped completely. There was no hope of getting there, and the Seryogins apparently decided that it would be better if no one reopened this wound...
My dad had already graduated from school at that time, as predicted for him - with a gold medal and entered the literary institute in absentia. To help his family, he worked as a journalist for the Izvestia newspaper, and in his spare time he began writing plays for the Russian Drama Theater in Lithuania.
Everything seemed to be fine, except for one very painful problem - since dad was an excellent speaker (for which, from my memory, he really had a very great talent!), the Komsomol committee of our town did not leave him alone, wanting to get him as their secretary. Dad resisted with all his might, because (even without knowing about his past, which the Seryogins decided not to tell him about for now) he hated revolution and communism with all his soul, with all the consequences arising from these “teachings,” and no “sympathies” for them did not feed... At school, he, naturally, was a pioneer and a Komsomol member, since without this it was impossible in those days to dream of entering any institute, but he categorically did not want to go beyond that. And also, there was one more fact that brought dad into real horror - this was participation in punitive expeditions against the so-called “forest brothers”, who were nothing more than just guys as young as dad, “dispossessed” guys » parents who hid in the forests so as not to be taken to the distant and very frightening Siberia.
For several years after the advent of Soviet power, there was not a family left in Lithuania from which at least one person was not taken to Siberia, and very often the whole family was taken away.
Lithuania was a small but very rich country, with an excellent economy and huge farms, the owners of which in Soviet times began to be called “kulaks”, and the same Soviet government began to very actively “dekulakize” them... And it was precisely for these “punitive expeditions” “The best Komsomol members were selected to show others an “infectious example”... These were friends and acquaintances of the same “forest brothers” who went to the same schools together, played together, went to dances with the girls... And now, on someone’s crazy order, suddenly for some reason they became enemies and had to exterminate each other...
After two such trips, in one of which two of the twenty guys who had left returned (and dad turned out to be one of these two), he got drunk half to death and the next day wrote a statement in which he categorically refused further participation in any this kind of"events". The first “pleasure” that followed after such a statement was the loss of his job, which at that time he “desperately” needed. But since dad was a truly talented journalist, he was immediately offered a job by another newspaper, Kaunasskaya Pravda, from a neighboring town. But, unfortunately, he didn’t have to stay there for long either, for such a simple reason as a short call “from above”... which instantly deprived dad of the new job he had just received. And dad was once again politely escorted out the door. Thus began his long-term war for the freedom of his personality, which even I remembered very well.
So, a few days ago, at the request of one of my friends, I gave a presentation on how to actually create analytical solutions based on BI products from Microsoft. And so that, in principle, others can understand how simply this is done, I will simply describe a fairly simple scenario for creating such a solution.
Actually, where do we start? Well, firstly, we must decide whether we need to have such a large application solution B.I.. Or you can get by with some other tool, for example Excel or others similar. I do not argue, Excel is very good product for analysis, but enough for analysis large set it does not fit the given data. But there is a way out - to put, for example, add-on which you all know perfectly well - PowerPivot. But here there is a snag, PowerPivot only fits Excel 2010(And we have both 2003 and 2007).
Let's pretend that we really need a solution based on Analysis Services and that other products are simply not suitable for us due to their limitations. After this, we can begin to construct an analytical solution. Let's start with this: imagine that we have already designed a data warehouse and loaded it into it. Simply put, we will create an analytical solution based on a ready-made data warehouse. Let's get started)
As the main tool we will use Business Intelligence Development Studio(BIDS)– which is based on the shell Visual Studio . Let's launch BIDS.
And we see the standard interface VS. After this we select File-New-Project(Ctrl+Shift+N). The window for creating a new project is displayed to us.
We will create a project Analysis Services. Let's enter a name in the Name field, let's say Analysis Services Project3. Select the path where the project will be saved and click OK. Visual Studio will prepare a project template for us, on the basis of which we will build a solution.
Next, in order to start building analytics, we need to create a data source( Data Sources) and sample from this source, creating Data Source Views. Let's do this! Right click on the folder Data Sources V Solution Explorer and choose New Data Source. The creation window will appear Data Source.
Click Next. We see a window for selecting a connection to a data source. Since we don't have any connection, we must create one. Click on New, the window you see below will be displayed.
In this window we can choose which source and with what parameters to connect. As you can see, there are quite a lot of data providers available, in two groups .NET And OLE DB.
So, we will connect to the database MS SQL Server, so in the field Server Name we enter the server name (for demonstration purposes I will use local SQL Server), we also select the type of authentication and the database with which we will work (I will use the demo database AdventureWorks). If you have selected everything, click OK, and then Next. In the window Impersonation Information we need to choose which one account AS will connect to the data source. For ease of demonstration I will choose Inherit(That is, it will take Service Account account). Click Next and pay attention to the connection string that the wizard created for us. If everything is fine, click Finish. Next, we need to extract the data we need from this source (which we will analyze), for this we will create Data Source View. Right click on the folder Data Source Views V Solution Explorer and choose New Data Source View. In the window that appears, click Next, select the source that we created in the previous step and click Next. In the table selection window, let's mark those tables that we need to build a multidimensional solution. Let's choose FactInternetSales and click on the more sign (>) . Our table will be added to the included objects. Click on the button Add Related Table to add tables that are related to the table FactInternetSales and click Next. In the final window, see if we have selected all the tables. If everything is correct, feel free to click Finish. We get a diagram of the connections of the tables that we loaded.
So what have we done? We have prepared the basis for creating a multidimensional solution, which we will call a cube. In order to create a cube, we from context menu on the folder Cubes let's choose New Cube. Click Next twice. We get into the selection window for a group of measures (the Group of Measures, as you remember, is a set of values, namely the numerical values that we will analyze), select the table FactInternetSales.
Click Next. In the window that appears, we can select only those measures that we want to be included in the analytical cube. We will leave everything as is, but if you need to remove something, you can always do it. Let's go ahead and click Next. In the next window, we need to select the measurement tables (Dimensions, as you remember, are used to perform slices by any values, that is, reference data is stored in the dimensions). Leave it as is, click Next. We check whether we have done everything correctly and click Finish.
So, we have created a cube, which we will use to build analytics. What, you ask, can you do with this cube? Well, right now nothing will work out))) Oddly enough, but first we need to expand our cube and carry out processing( Deploy and Process).
The deployment process is very simple, the files that we created are collected in the so-called AS database and then deployed on the AS instance.
Processing is a mechanism that ensures the construction of the cube structure, as well as the loading of data into dimensions and measures, and, accordingly, the calculation of aggregates.
Since the cube structure is stored in XML-like file, then for any change in the structure of the cube we need to perform Deploy. If we want to rebuild the structure of the cube regarding the changes we have made and update the data in the measures and dimensions, we are required to do Processing.
Let's do it Deploy and Process, to do this, right-click on the cube and select Process. We will be notified that the cube did not go through the deployment stage. Click Yes to start the deployment process.
After the deployment process is completed, we have a window that will start processing the cube.
Click Run to start processing (I’ll talk about additional processing options another time). Processing begins in front of us, we wait for the end of this process and click Close twice.
The cube is ready to go! We can begin to analyze. For more convenient use cube, let's try to open it using a well-known tool Excel. Let's launch Excel, go to the tab Data and select connection to AS
In the window that appears, enter the server name and click Next. Select our cube that we created in the previous step and click Finish
First, a little about how I approached this. The bosses said that it would be nice to deploy some kind of analytics on the existing database. After doing a little Google search, it turned out that it would be perfect OLAP technology. And since MS SQL Server is deployed on the company’s server, the Analysis Services component there is even more suitable.
Rubbing my hands joyfully, I grabbed the Microsoft textbook that comes with SQL Server. And two days later I was sure that I could do everything. But that was not the case... In the existing database, most of the relationships between the tables turned out to be relationships many-to-many, which at first did not foreshadow any complications. But in practice it turns out that additional very meaningful hand waving is not possible, because otherwise the cube simply gives out incorrect information.
Finding information on this topic in RuNet turned out to be not an easy task. Either this is something that everyone takes for granted, or the skis don’t work. However, I did find a very cool, large English manual on this topic. Actually, those who understand English perfectly or want to understand the topic very well are invited not to read further, but to follow the link: http://www.sqlbi.com/articles/many2many/
Those who, like me earlier, do not have time to read English textbooks, read further. The post is not a translation this manual: rather a kind of summation of his first examples + some basics from the Microsoft mentioned textbook.
Actually that's the problem. If the reader has read this post: http://habrahabr.ru/post/67272/, then he knows that standard schemes for Olap are the “Star” and “Snowflake” schemes. What if we are surrounded by many-to-many connections?
The simplest option: get away from many-to-many relationships using views - this will have a positive effect on the speed of request processing. Can't leave? Let's figure it out.
Formulation of the problem. Online store. MS SQL Server, it contains a database with the notorious M2M connections, which looks like this: A table of purchase identifiers, a category table (food, sports, other) and an account table are linked to it. Let’s complicate the task: let now several people can use one account at once (for example, a husband and wife are making a purchase for a house), respectively, a table of persons is connected through M2M. And so that it’s not sugary at all: let a table of person categories be linked to the table of persons via M2M. And we are interested in: what categories of people make what purchases more often, and when they do it.
The example is far-fetched, but the problem is still visible: how to connect a future dimension with a fact table through as many as two M2M connections? It's simple, we'll tell SSAS where to look.
Having completed preliminary actions on creating dimensions (Types, Dates, Categories, Persons, Accounts) (see. http://habrahabr.ru/post/67272/), we are trying to create a cube using the Sales measure (number of rows). By default, Visual Studio will offer us only three measures (Types, Accounts, Dates) - because only they are directly related to our measure. Having created a cube, we add the remaining two measures with our hands. Plus, we create two more auxiliary measures inside the cube, which will be responsible for processing the M2M connection: Bridge Accounts Persons and Bridge Persons Categories (both are the number of rows in which tables are clear).
Thus, we will have the following picture:
We see a lot of gray boxes and the fact that Visual Studio has already processed one M2M relationship: between the Bridge Persons Categories auxiliary measure and the Accounts dimension. And that's good, but not enough. If we ask our cube to spit out something right now, we won't get anything good. No, the Dates and Accounts dimensions will do everything right, but unfortunately with Persons and Categories.
To correct this misunderstanding, we will tell SSAS where to look for information to process our requests. To do this, fill in those gray boxes on the “Using Dimensions” tab as follows: click on the gray boxes -> ellipses -> type of relationship, select “Many to many” -> Select an intermediate group of measures, as written in the picture:
Voila! Everything is working. You can see it in the next picture:
What's the magic? We've told SSAS where and how to look for relationship information. Please note: it is impossible, for example, to correctly fill out the intersection of the “Categories dimension and the Sales measure” until you have correctly filled out the “Categories dimension and the Bridge Account Persons measure.” Only Bridge Persons Categories appears in the options, because... Visual Studio simply doesn't know any other way. But auxiliary measures are also measures. And the paths for them must be indicated in the same way as for ordinary (targeted) measures. As the table populates, Visual Studio gains knowledge and offers more options.
Now let’s formulate a mnemonic rule on how to fill out the relationship table: “Between the target measure and the target dimension, choose the closest table of measures to the target measure.” Thus, it turned out that for the Type and Date dimensions there would be a full-fledged, interesting Sales measure in the second column, and an auxiliary Bridge Accounts Persons measure in the third. The same is true for the Categories dimension.
Should I always fill out the link table completely? No. There is no need to process uninteresting information. M2M connections have a negative impact on cube performance, so it's best to get rid of them if possible.
That's all for this. I hope I had a useful post. At a minimum, I can say that at one time I was looking for exactly something like this, but I never found it. I can send you the source code for the database and cube from the example.
Microsoft SQL Server Analysis Services (SSAS) is the basic platform for the development of business intelligence systems. SSAS 2008 provide high performance application performance and scalability at the level of millions of records and thousands of users.
Components of the Microsoft BI solution. The BI solution is based on a scalable platform designed for data integration, building data warehouses, analyzing accumulated data and generating reports. The core of the BI solution is the SQL Server platform. Components: SQL Server Database Engine – A scalable, high-performance DBMS capable of storing large volumes of data resulting from data consolidation into a single repository for analysis and reporting. SQL Server Integration Services – A platform for performing extraction, transformation and loading operations that ensures that the database is populated and synchronized with data from various sources, which are used by the organization's business applications. SQL Server Analysis Services – Provides the ability to build OLAP solutions, including the ability to calculate key performance indicators (KPIs). SQL Server Reporting Services – A reporting toolkit designed to create, publish and distribute detailed business reports for both internal and external purposes.
UDM. SSAS are built on the Unified Dimensional Model (UDM), which allows various types client applications Access data from both relational and multidimensional databases without using separate models for each type of database.
The basis of UDM is an attribute-based dimension architecture, which makes it possible to group the properties that define the functioning of a business into one dimension and separate these properties from the rules for navigation along the dimension - hierarchies.
UDM provides the ability to use multiple data sources to create a multidimensional model. UDM can be used to create a unified view of relational and multidimensional data, including business objects, business intelligence, calculations and metrics; creates an intermediate logical layer between the physical relational database used as a data source and the proprietary cube and dimension structures used to process user queries. The UDM model can be thought of as a kernel OLAP systems. One of the key advantages of the UDM model is the ability to combine the flexibility and functional richness of traditional relational model generate reports with powerful analytics and superior performance classic model OLAP systems.
Scalability and performance
Due to its high scalability, SSAS services allow you to work with terabyte databases and thousands of users. To support large numbers of users, avoid resource contention, and reduce costs, SSAS can be scaled out. Horizontal scaling is to build up computing power and data storage capacity to store and synchronize multiple versions of data, but at the same time SSAS services allow you to organize general access to read information from a single service database from multiple servers, eliminating the need for redundant resources.
SSAS cubes are multi-dimensional structures that provide high-speed access to large volumes of pre-aggregated data, allowing end users to obtain real-time business data of interest. SSAS stores business data in MOLAP format, which provides highly optimized and compressed data.
Data Source View
SSAS provides an abstraction layer over the relational data source - the data source view. The data source view allows you to define tables from relational base for use in a multidimensional model. In addition, the data source view object allows you to create calculated columns and views on top of relational tables.
Integration with Microsoft Office System 2007
The Microsoft BI platform provides integration capabilities with products of the Microsoft Office System 2007 family, in particular:
Microsoft Office Excel makes it possible to view data stored in SSAS OLAP cubes by building dynamic Microsoft PivotTable views, which does not require installing additional software;
Microsoft Office Word allows you to view reports generated using Reporting Services;
Microsoft Office Visio allows you to visualize decision trees, dependency trees, cluster diagrams and other models data technology mining;
Microsoft Office SharePoint Server allows you to create a single user interface to view and manage reports generated using Reporting Services. Localization of the solution through the use of translations
There is often a need to develop multilingual solutions. As a rule, the data itself is the same for the whole world, but the metadata - cube, measures, names and levels of measurements, key performance indicators (KPI) will be different for each language used. Translations allow you to specify different metadata values for different languages and tailor solutions to work in an international context. Financial information also needs to be localized to present results in the correct currency. SSAS's translation and automatic currency conversion capabilities enable you to display localized analysis data on native language users.
(DBMS). Microsoft has included a set of services in SQL Server related to business intelligence and data warehousing. These services include Integration Services and Analysis Services. Analysis Services, in turn, includes a set of tools for working with OLAP and data mining.
Story
Chronology
The following are the major events in the history of Microsoft Analysis Services, covering the period since 1996.
| date | Event |
|---|---|
| 1996-07-01 | Microsoft is recruiting a new team of developers to create an OLAP product codenamed Plato (rearranging the letters of the OLAP acronym) |
| 1996-07-15 | Panorama Software delegation meets with Microsoft representatives |
| 1996-10-27 | Microsoft announces acquisition of Panorama Software development team |
| 1998-11 | OLAP Services 7.0 is released (codename Sphinx) |
| 2000-08 | Analysis Services 2000 is released (codenamed Shiloh) |
| 2001-11 | XML for Analysis SDK 1.0 released |
| 2003-04 | ADOMD.NET and XML for Analysis SDK 1.1 is released |
| 2005-10-28 | Analysis Services 2005 (codenamed Yukon) is released |
| 2008-08-06 | Analysis Services 2008 is released (codenamed Katmai) |
Storage Modes
Microsoft Analysis Services is neutral in the MOLAP vs. ROLAP debate surrounding OLAP products. Thanks to this, you can use all types of MOLAP, ROLAP and HOLAP within one model.
Separate storage modes
- MOLAP - Multidimensional OLAP (multidimensional OLAP) - The data itself and its processing are processed, stored and indexed using a special format optimized for multidimensional data.
- ROLAP - Relational OLAP - Data and its processing remain in the relational data source, eliminating the need for special processing.
- HOLAP - Hybrid OLAP (hybrid OLAP) - this mode uses a relational data source to store the data itself, but preprocessing and indexes are stored in a special format optimized for multidimensional data.
Dimensional storage modes
- MOLAP - dimension and hierarchy attributes are processed and stored in a special format
- ROLAP - dimension attributes are not processed and remain in the relational data source. Partitions measured by the ROLAP dimension must also be in ROLAP mode.
Programming interfaces and object models
Microsoft Analysis Services supports different sets of application programming interfaces (APIs) and object models for different operations in different software environments.
Data extraction
- XML for Analysis is the lowest level API. Can be used on any platform and with any programming language that supports HTTP and XML
- OLE DB for OLAP - OLEDB extension. Based on COM and intended for use in C/C++ programs on the Windows platform.
- ADOMD - ADO extension. Based on the COM Windows platform.
- ADOMD.NET - ADO.NET extension. Based on .NET managed code on CLR platforms.
Administration and management
- DSO - For AS 2000. Based on COM Automation and intended for programs on the Windows platform.
- AMO - For AS 2005. Based on .NET technology and intended for programs written using managed code on CLR platforms.
Query languages
Microsoft Analysis Services supports the following query languages:
Data Definition Language (DDL)
DDL (Data Definition Language) in Analysis Services is based on XML and supports commands such as
Data Manipulation Language (DML)
- MDX - for querying OLAP cubes of a multidimensional model
- DAX - for querying OLAP cubes of a tabular model ( D ata A nalysis e X pressures, PowerPivot)
- SQL - a limited subset of SQL statements for querying OLAP cubes and treating dimensions as tables
- DMX - for querying data mining models
Write a review about the article "Microsoft Analysis Services"
Notes
Literature
- Sivakumar Harinath, Matt Carroll and others. Microsoft SQL Server Analysis Services 2008 and MDX for Professionals = Professional Microsoft SQL Server Analysis Services 2008 with MDX. - M.: “Dialectics”, 2010. - P. 1072. - ISBN 978-5-8459-1636-5.
- Sivakumar Harinath, Stephen Quinn: Professional SQL Server Analysis Services 2005 with MDX. ISBN 0-7645-7918-5
- Teo Lachev: Applied Microsoft Analysis Services 2005: And Microsoft Business Intelligence Platform. ISBN 0-9766353-0-5
- Reed Jacobson: Microsoft(r) SQL Server(tm) 2000 Analysis Services Step by Step. ISBN 0-7356-0904-7
- Claude Seidman: Data Mining with Microsoft SQL Server 2000 Technical Reference ."" ISBN 0-7356-1271-4
- George Spofford: MDX-Solutions. Wiley, 2001, ISBN 0-471-40046-7
- Mosha Pasumansky, Mark Whitehorn, Rob Zare: Fast Track to MDX. ISBN 1-84628-174-1
- ZhaoHui Tang, Jamie MacLennan: Data Mining with SQL Server 2005. ISBN 0-471-46261-6
- Edward Melomed, Irina Gorbach, Alexander Berger, Py Bateman: Microsoft SQL Server 2005 Analysis Services. ISBN 0-672-32782-1
Links
- (English)
- (English)
- (English)
- (English)
- (English)
- (English)
|
||||||||||||||||||||||||||||||||||||||||
Excerpt describing Microsoft Analysis Services
Pierre, as one of the most honored guests, was to sit in Boston with Ilya Andreich, the general and colonel. Pierre had to sit opposite Natasha at the Boston table, and the strange change that had occurred in her since the day of the ball amazed him. Natasha was silent, and not only was she not as good-looking as she was at the ball, but she would have been bad if she had not looked so meek and indifferent to everything.
"What with her?" thought Pierre, looking at her. She sat next to her sister at the tea table and reluctantly, without looking at him, answered something to Boris, who sat down next to her. Having walked away the whole suit and taken five bribes to the satisfaction of his partner, Pierre, who heard the chatter of greetings and the sound of someone’s steps entering the room while collecting bribes, looked at her again.
“What happened to her?” he said to himself even more surprised.
Prince Andrei stood in front of her with a thrifty, tender expression and told her something. She, raising her head, flushed and apparently trying to control her gusty breathing, looked at him. And the bright light of some inner, previously extinguished fire burned in her again. She was completely transformed. From being bad she again became the same as she was at the ball.
Prince Andrei approached Pierre and Pierre noticed a new, youthful expression on his friend’s face.
Pierre changed seats several times during the game, now with his back, now facing Natasha, and throughout the entire 6 Roberts made observations of her and his friend.
“Something very important is happening between them,” thought Pierre, and the joyful and at the same time bitter feeling made him worry and forget about the game.
After 6 Roberts, the general stood up, saying that it was impossible to play like that, and Pierre received his freedom. Natasha was talking to Sonya and Boris on one side, Vera was talking about something with a subtle smile to Prince Andrei. Pierre went up to his friend and, asking if what was being said was a secret, sat down next to them. Vera, noticing Prince Andrei's attention to Natasha, found that at an evening, at a real evening, it was necessary that there be subtle hints of feelings, and seizing the time when Prince Andrei was alone, she began a conversation with him about feelings in general and about her sister . With such an intelligent guest (as she considered Prince Andrei) she needed to apply her diplomatic skills to the matter.
When Pierre approached them, he noticed that Vera was in a smug rapture of conversation, Prince Andrei (which rarely happened to him) seemed embarrassed.
– What do you think? – Vera said with a subtle smile. “You, prince, are so insightful and so immediately understand the character of people.” What do you think about Natalie, can she be constant in her affections, can she, like other women (Vera meant herself), love a person once and remain faithful to him forever? This is what I consider true love. What do you think, prince?
“I know your sister too little,” answered Prince Andrei with a mocking smile, under which he wanted to hide his embarrassment, “to resolve such a delicate question; and then I noticed that the less I like a woman, the more constant she is,” he added and looked at Pierre, who came up to them at that time.
- Yes, it’s true, prince; in our time,” Vera continued (mentioning our time, as narrow-minded people generally like to mention, believing that they have found and appreciated the features of our time and that the properties of people change over time), in our time a girl has so much freedom that le plaisir d"etre courtisee [the pleasure of having admirers] often drowns out the true feeling in her. Et Nathalie, il faut l"avouer, y est tres sensible. [And Natalya, I must admit, is very sensitive to this.] The return to Natalie again made Prince Andrei frown unpleasantly; he wanted to get up, but Vera continued with an even more refined smile.
“I think no one was courtisee [the object of courtship] like her,” said Vera; - but never, until very recently, did she seriously like anyone. “You know, Count,” she turned to Pierre, “even our dear cousin Boris, who was, entre nous [between us], very, very dans le pays du tendre... [in the land of tenderness...]
Prince Andrei frowned and remained silent.
– You’re friends with Boris, aren’t you? – Vera told him.
- Yes, I know him…
– Did he tell you correctly about his childhood love for Natasha?
– Was there childhood love? - Prince Andrei suddenly asked, blushing unexpectedly.
- Yes. Vous savez entre cousin et cousine cette intimate mene quelquefois a l"amour: le cousinage est un dangereux voisinage, N"est ce pas? [You know, between a cousin and sister, this closeness sometimes leads to love. Such kinship is a dangerous neighborhood. Is not it?]
“Oh, without a doubt,” said Prince Andrei, and suddenly, unnaturally animated, he began joking with Pierre about how he should be careful in his treatment of his 50-year-old Moscow cousins, and in the middle of the joking conversation he stood up and, taking under Pierre's arm and took him aside.
- Well? - said Pierre, looking with surprise at the strange animation of his friend and noticing the look that he cast at Natasha as he stood up.
“I need, I need to talk to you,” said Prince Andrei. – You know our women’s gloves (he was talking about those Masonic gloves that were given to a newly elected brother to give to his beloved woman). “I... But no, I’ll talk to you later...” And with a strange sparkle in his eyes and anxiety in his movements, Prince Andrei approached Natasha and sat down next to her. Pierre saw Prince Andrei ask her something, and she flushed and answered him.
But at this time Berg approached Pierre, urgently asking him to take part in the dispute between the general and the colonel about Spanish affairs.
Berg was pleased and happy. The smile of joy did not leave his face. The evening was very good and exactly like other evenings he had seen. Everything was similar. And ladies', delicate conversations, and cards, and a general at cards, raising his voice, and a samovar, and cookies; but one thing was still missing, something that he always saw at the evenings, which he wanted to imitate.
There was a lack of loud conversation between men and an argument about something important and smart. The general began this conversation and Berg attracted Pierre to him.
The next day, Prince Andrei went to the Rostovs for dinner, as Count Ilya Andreich called him, and spent the whole day with them.
Everyone in the house felt for whom Prince Andrei was traveling, and he, without hiding, tried to be with Natasha all day. Not only in Natasha’s frightened, but happy and enthusiastic soul, but in the whole house one could feel the fear of something important that was about to happen. The Countess looked at Prince Andrei with sad and seriously stern eyes when he spoke to Natasha, and timidly and feignedly began some insignificant conversation as soon as he looked back at her. Sonya was afraid to leave Natasha and was afraid to be a hindrance when she was with them. Natasha turned pale with fear of anticipation when she remained alone with him for minutes. Prince Andrei amazed her with his timidity. She felt that he needed to tell her something, but that he could not bring himself to do so.
When Prince Andrey left in the evening, the Countess came up to Natasha and said in a whisper:
- Well?
“Mom, for God’s sake don’t ask me anything now.” “You can’t say that,” Natasha said.
But despite this, that evening Natasha, sometimes excited, sometimes frightened, with fixed eyes, lay for a long time in her mother’s bed. Either she told her how he praised her, then how he said that he would go abroad, then how he asked where they would live this summer, then how he asked her about Boris.
