![[MS-DPSSAS]: SQL Server Analysis Services Data Portability Overview](/img/37/5bms-dpssas-5d.jpg)
Transcription
[MS-DPSSAS]:SQL Server Analysis Services Data Portability OverviewIntellectual Property Rights Notice for Open Specifications Documentation Technical Documentation. Microsoft publishes Open Specifications documentation (“thisdocumentation”) for protocols, file formats, data portability, computer languages, and standardssupport. Additionally, overview documents cover inter-protocol relationships and interactions.Copyrights. This documentation is covered by Microsoft copyrights. Regardless of any otherterms that are contained in the terms of use for the Microsoft website that hosts thisdocumentation, you can make copies of it in order to develop implementations of the technologiesthat are described in this documentation and can distribute portions of it in your implementationsthat use these technologies or in your documentation as necessary to properly document theimplementation. You can also distribute in your implementation, with or without modification, anyschemas, IDLs, or code samples that are included in the documentation. This permission alsoapplies to any documents that are referenced in the Open Specifications documentation.No Trade Secrets. Microsoft does not claim any trade secret rights in this documentation.Patents. Microsoft has patents that might cover your implementations of the technologiesdescribed in the Open Specifications documentation. Neither this notice nor Microsoft's delivery ofthis documentation grants any licenses under those patents or any other Microsoft patents.However, a given Open Specifications document might be covered by the Microsoft OpenSpecifications Promise or the Microsoft Community Promise. If you would prefer a written license,or if the technologies described in this documentation are not covered by the Open SpecificationsPromise or Community Promise, as applicable, patent licenses are available by contactingiplg@microsoft.com.License Programs. To see all of the protocols in scope under a specific license program and theassociated patents, visit the Patent Map.Trademarks. The names of companies and products contained in this documentation might becovered by trademarks or similar intellectual property rights. This notice does not grant anylicenses under those rights. For a list of Microsoft trademarks, visitwww.microsoft.com/trademarks.Fictitious Names. The example companies, organizations, products, domain names, emailaddresses, logos, people, places, and events that are depicted in this documentation are fictitious.No association with any real company, organization, product, domain name, email address, logo,person, place, or event is intended or should be inferred.Reservation of Rights. All other rights are reserved, and this notice does not grant any rights otherthan as specifically described above, whether by implication, estoppel, or otherwise.Tools. The Open Specifications documentation does not require the use of Microsoft programmingtools or programming environments in order for you to develop an implementation. If you have accessto Microsoft programming tools and environments, you are free to take advantage of them. CertainOpen Specifications documents are intended for use in conjunction with publicly available standardsspecifications and network programming art and, as such, assume that the reader either is familiarwith the aforementioned material or has immediate access to it.Support. For questions and support, please contact dochelp@microsoft.com.1 / 13[MS-DPSSAS] - v20170816SQL Server Analysis Services Data Portability OverviewCopyright 2017 Microsoft CorporationRelease: August 16, 2017
Revision /20100.1MajorFirst release.9/3/20100.1NoneNo changes to the meaning, language, or formatting of thetechnical content.2/9/20110.1NoneNo changes to the meaning, language, or formatting of thetechnical content.7/7/20110.1NoneNo changes to the meaning, language, or formatting of thetechnical content.11/3/20110.1NoneNo changes to the meaning, language, or formatting of thetechnical content.1/19/20121.1MinorClarified the meaning of the technical content.2/23/20121.1NoneNo changes to the meaning, language, or formatting of thetechnical content.3/27/20121.1NoneNo changes to the meaning, language, or formatting of thetechnical content.5/24/20121.1NoneNo changes to the meaning, language, or formatting of thetechnical content.6/29/20121.1NoneNo changes to the meaning, language, or formatting of thetechnical content.7/16/20121.1NoneNo changes to the meaning, language, or formatting of thetechnical content.10/8/20121.1NoneNo changes to the meaning, language, or formatting of thetechnical content.10/23/20121.1NoneNo changes to the meaning, language, or formatting of thetechnical content.3/26/20131.1NoneNo changes to the meaning, language, or formatting of thetechnical content.6/11/20131.1NoneNo changes to the meaning, language, or formatting of thetechnical content.8/8/20131.1NoneNo changes to the meaning, language, or formatting of thetechnical content.12/5/20131.1NoneNo changes to the meaning, language, or formatting of thetechnical content.2/11/20141.1NoneNo changes to the meaning, language, or formatting of thetechnical content.5/20/20141.1NoneNo changes to the meaning, language, or formatting of thetechnical content.5/10/20162.0MajorSignificantly changed the technical content.8/16/20173.0MajorSignificantly changed the technical content.2 / 13[MS-DPSSAS] - v20170816SQL Server Analysis Services Data Portability OverviewCopyright 2017 Microsoft CorporationRelease: August 16, 2017
Table of Contents1Introduction . 41.1Glossary . 41.2References . 52Data Portability Scenarios . 62.1Exporting Metadata . 62.1.1Data Description . 62.1.1.1Unified Dimensional Model . 62.1.1.2Data Mining . 82.1.2Format and Protocol Summary . 82.1.3Data Portability Methodology. 82.1.3.1Using Microsoft SQL Server Management Studio . 82.1.3.2Using Analysis Management Objects . 92.1.3.3Preconditions . 92.1.3.4Versioning . 92.1.3.5Error Handling . 92.1.3.6Coherency Requirements . 92.1.3.7Additional Considerations . 92.2Exporting Writeback Data. 92.2.1Data Description . 102.2.1.1Dimension Writeback . 102.2.1.2Cube and Partition Writeback. 102.2.2Format and Protocol Summary . 102.2.3Data Portability Methodology . 102.2.3.1Preconditions . 112.2.3.2Versioning . 112.2.3.3Error Handling . 112.2.3.4Coherency Requirements . 112.2.3.5Additional Considerations . 113Change Tracking . 124Index . 133 / 13[MS-DPSSAS] - v20170816SQL Server Analysis Services Data Portability OverviewCopyright 2017 Microsoft CorporationRelease: August 16, 2017
1IntroductionThe SQL Server Analysis Services Data Portability Overview document provides an overview of dataportability scenarios between SQL Server Analysis Services and a vendor’s application. AnalysisServices provides a business intelligence (BI) platform that enables end users and IT professionals toefficiently analyze business data.Two primary workloads exist for achieving this: Corporate BI: In this mode, IT professionals use Business Intelligence Development Studio andMicrosoft SQL Server Management Studio to build and manage Analysis Services installations. Self-Service BI: In this mode, end users build their own solutions by using PowerPivottechnologies.In either mode, Analysis Services databases are built and used by the client tools. Unless specifiedotherwise, concepts and mechanisms described in this document are applicable to both workloads.1.1GlossaryThis document uses the following terms:analysis server: A server that supports high performance and complex analytics for businessintelligence applications.cube: A set of data that is organized and summarized into a multidimensional structure that isdefined by a set of dimensions and measures.dimension: A structural attribute of a cube, which is an organized hierarchy of categories (levels)that describe data in a fact table. These categories typically describe a similar set of membersupon which the user bases an analysis.hierarchy: A logical tree structure that organizes the members of a dimension such that eachmember has one parent member and zero or more child members.JavaScript Object Notation (JSON): A text-based, data interchange format that is used totransmit structured data, typically in Asynchronous JavaScript XML (AJAX) web applications,as described in [RFC7159]. The JSON format is based on the structure of ECMAScript (Jscript,JavaScript) objects.measure: In a cube, a set of values that are typically numeric and are based on a column in thefact table of the cube. Measures are the central values that are aggregated and analyzed.partition: One of the storage containers for data and aggregations of a cube. Every cube containsone or more partitions. For a cube with multiple partitions, each partition can be storedseparately in a different physical location. Each partition can be based on a different datasource. Partitions are not visible to users; the cube appears to be a single object.schema: The set of attributes and object classes that govern the creation and update of objects.XML: The Extensible Markup Language, as described in [XML1.0].XML schema definition (XSD): The World Wide Web Consortium (W3C) standard language thatis used in defining XML schemas. Schemas are useful for enforcing structure and constrainingthe types of data that can be used validly within other XML documents. XML schema definitionrefers to the fully specified and currently recommended standard for use in authoring XMLschemas.4 / 13[MS-DPSSAS] - v20170816SQL Server Analysis Services Data Portability OverviewCopyright 2017 Microsoft CorporationRelease: August 16, 2017
1.2ReferencesLinks to a document in the Microsoft Open Specifications library point to the correct section in themost recently published version of the referenced document. However, because individual documentsin the library are not updated at the same time, the section numbers in the documents may notmatch. You can confirm the correct section numbering by checking the Errata.[MS-SSAS-T] Microsoft Corporation, "SQL Server Analysis Services Tabular".[MS-SSAS] Microsoft Corporation, "SQL Server Analysis Services Protocol".[MSDN-AMO] Microsoft Corporation, "Developing with Analysis Management Objects 24924.aspx[MSDN-BIDS] Microsoft Corporation, "Introducing Business Intelligence Development 173767.aspx[MSDN-PROC] Microsoft Corporation, "Analysis Services 2005 Processing ary/ms345142(SQL.90).aspx[MSDN-SSMS] Microsoft Corporation, "Use SQL Server Management 174173.aspx[MSDN-UDM] Microsoft Corporation, "Unified Dimensional Model", L.90).aspx[MSFT-DM] Microsoft Corporation, "Data Mining Concepts", .aspx[MSFT-WBDIM] Microsoft Corporation, "Write-Enabled Dimensions", .aspx[MSFT-WBPT] Microsoft Corporation, "Write-Enabled Partitions", .aspx5 / 13[MS-DPSSAS] - v20170816SQL Server Analysis Services Data Portability OverviewCopyright 2017 Microsoft CorporationRelease: August 16, 2017
2Data Portability ScenariosAnalysis Services imports data from a variety of data sources and makes this data available foranalysis by end users. Analysis Services architecture is designed with the assumption that theunderlying data source is the master store of this data. This assumption holds for data pushed toAnalysis Services through a mechanism called push-mode processing. For more information aboutpush-mode processing, see [MSDN-PROC]. As such, Analysis Services does not provide an efficientbulk data export utility and instead depends on the capabilities of the underlying data source for thispurpose.At the same time, Analysis Services provides support for exporting the definition of objects defined byIT professionals. In addition, Analysis Services enables end users to write data back into AnalysisServices. This section describes how to export this data.2.1Exporting MetadataThird-party applications can export definitions of user-created objects stored within Analysis Services.2.1.1 Data Description2.1.1.1 Unified Dimensional ModelThe Analysis Services metadata model, called a Unified Dimensional Model (UDM), provides a bridgebetween users and the data sources. A UDM is constructed over one or more physical data sources,and it allows end-user queries using one of a variety of client tools, such as Microsoft Office Excel.6 / 13[MS-DPSSAS] - v20170816SQL Server Analysis Services Data Portability OverviewCopyright 2017 Microsoft CorporationRelease: August 16, 2017
Figure 1: Unified Dimensional ModelA UDM contains information about: Data source connections. A schema snapshot for data that exists in a data source. The user-visible concepts, such as dimensions, hierarchies, and key performance indicators. Mapping between the user-visible concepts and the underlying data sources. Calculations that encapsulate business logic, such as a three-month moving average. Security roles and associated authorizations.For more information about the UDM, see [MSDN-UDM].A UDM is typically defined by IT professionals using Business Intelligence Development Studio. Duringthe development process, UDM metadata is stored in proprietary XML-based files. Once this UDMdefinition is complete, it can be deployed by Business Intelligence Development Studio to an analysisserver by using the SQL Server Analysis Services Protocol [MS-SSAS] where it is stored in aproprietary format. For more information about Business Intelligence Development Studio, see[MSDN-BIDS].7 / 13[MS-DPSSAS] - v20170816SQL Server Analysis Services Data Portability OverviewCopyright 2017 Microsoft CorporationRelease: August 16, 2017
2.1.1.2 Data MiningData mining is the process of discovering actionable information from data by using variousmathematical analysis techniques. Analysis Services provides data mining support. Within AnalysisServices, data mining information is specified as part of a database. For more information about datamining, see [MSFT-DM].Client tools use the SQL Server Analysis Services Protocol [MS-SSAS] for communicating with bothUDM and data mining on an analysis server.2.1.2 Format and Protocol SummaryThe following table provides a comprehensive list of the formats and protocols used in this dataportability scenario.Protocol or formatnameDescriptionReferenceSQL Server AnalysisServices ProtocolSpecifies methods for a client to communicate with, and performoperations on, an analysis server.[MS-SSAS]SQL Server AnalysisServices TabularSpecifies methods for a client to communicate with, and performoperations on, an analysis server Tabular database.[MS-SSAS-T]2.1.3 Data Portability Methodology2.1.3.1 Using Microsoft SQL Server Management StudioMicrosoft SQL Server Management Studio allows implementers to manage instances of AnalysisService servers. For more information about SQL Server Management Studio, see [MSDN-SSMS].To extract metadata by using SQL Server Management Studio, follow these steps:1. Connect to Analysis Services. For Analysis Services servers in multidimensional mode, please connect to the database byproviding the server name or servername\instancename. For PowerPivot workbooks, upload the PowerPivot workbook to a Microsoft PowerPivot forSharePoint server. Connect to the database by providing the URL of the PowerPivot workbookon the Microsoft SharePoint server. For Tabular projects in Business Intelligence Development Studio, deploy the Tabular Projectto Analysis Services running in Tabular mode. Connect to the database by providing the servername or servername\instance name.2. Select the database to be scripted. In the case of PowerPivot workbooks, you only have a singledatabase.3. Right-click the database object to see the context menu, and then select Script Database as.4. Retrieve the script to create the database. To do this, select CREATE To, and then specify thedestination of the script.The output of these steps results in XML content that contains all metadata objects within thedatabase and that conforms to the XML schema definition (XSD) and JSON schemas that aredocumented in [MS-SSAS] and [MS-SSAS-T].8 / 13[MS-DPSSAS] - v20170816SQL Server Analysis Services Data Portability OverviewCopyright 2017 Microsoft CorporationRelease: August 16, 2017
2.1.3.2 Using Analysis Management ObjectsThe Analysis Management Objects (AMO) object model enables implementers to programmaticallymanage a running instance of an Analysis Services database. For more information about AMO, see[MSDN-AMO].To extract metadata by using AMO, follow these steps:1. Use the Server.Connect() method to connect to Analysis Services.2. Initialize a System.Xml.XmlWriter instance, such as System.Xml.XmlTextWriter.3. Use the Server.Connect() method to connect to Analysis Services.4. Flush and close the System.Xml.XmlWriter instance.The output of these steps results in XML content that contains all metadata objects within thedatabase and that conforms to the XSD language documented in [MS-SSAS].2.1.3.3 PreconditionsTo extract the metadata from an Analysis Services database, an Analysis Services server is required tohave the database loaded, and this database is required to be accessible to the security principalexecuting the extraction commands.2.1.3.4 VersioningThe Tabular database aspects of this metadata export scenario that apply to compatibility levels 1200or higher are applicable to Microsoft SQL Server 2016 and later.2.1.3.5 Error HandlingNone.2.1.3.6 Coherency RequirementsThere are no special coherency requirements.2.1.3.7 Additional ConsiderationsAny secret (such as an explicit password) that is specified in a data source that is sent to a serverrunning Analysis Services cannot be retrieved and is required to be respecified.As an example, an Analysis Services server does not return passwords that are stored in a data sourceas part of properties: ConnectionString Password Credential2.2Exporting Writeback DataCertain Analysis Services deployments enable interactive updates to dimensions and partition data.Writeback features are supported only in Corporate BI mode and are not supported in Self-Service BImode.9 / 13[MS-DPSSAS] - v20170816SQL Server Analysis Services Data Portability OverviewCopyright 2017 Microsoft CorporationRelease: August 16, 2017
2.2.1 Data Description2.2.1.1 Dimension WritebackDimension writebacks allow implementers to change, move, add, and delete attribute members withina dimension. These updates are stored directly in the data source table, which serves as the sourcefor the dimension. For exporting this data, applications can query the underlying data source directly.For more information about dimension writeback, see [MSFT-WBDIM].2.2.1.2 Cube and Partition WritebackCube writebacks enable implementers to change measure data that is stored in a partition.Once an implementer enables a cube for writeback through Business Intelligence Development Studio,Analysis Services performs the following operations: Creates a writeback table in the underlying data source that stores changes made by the end useras a difference from the current value. For example, if an end user changes a cell value from 90 to100, the value 10 is stored in the writeback table, along with the time of the change andinformation about the end user who made the change. Creates a writeback partition within the cube that corresponds to the writeback table.The net effect of accumulated changes is displayed to client applications. The original value in thecube is preserved, and an audit trail of changes is recorded in the writeback table.For more information about write-enabled partitions, see [MSFT-WBPT].2.2.2 Format and Protocol SummaryThe following table provides a comprehensive list of the formats and protocols used in this dataportability scenario.Protocol or format nameDescriptionReferenceSQL Server AnalysisServices ProtocolSpecifies methods for a client to communicate with, and performoperations on, an analysis server.[MS-SSAS]2.2.3 Data Portability MethodologyWriteback partitions can be identified through SQL Server Analysis Services Protocol [MS-SSAS] orthrough AMO [MSDN-AMO]. In SQL Server Analysis Services Protocol, writeback partitions are Partition elements that havetheir Type element set to Writeback. In AMO, writeback partitions are objects of type Partition that have their Type property set toPartitionType.Writeback.Because the data source bindings of a writeback partition are similar to those of a regular partition,information about the table that is used for storing writeback data can be retrieved through theSource property of the writeback partition. In the TableBinding type, the table name is stored in theDbTableName property.10 / 13[MS-DPSSAS] - v20170816SQL Server Analysis Services Data Portability OverviewCopyright 2017 Microsoft CorporationRelease: August 16, 2017
2.2.3.1 PreconditionsTo retrieve information about writeback partitions, the partitions are required to be accessible on anAnalysis Services server by the security principal issuing discovery commands.2.2.3.2 VersioningNone.2.2.3.3 Error HandlingNone.2.2.3.4 Coherency RequirementsThere are no special coherency requirements.2.2.3.5 Additional ConsiderationsThere are no additional considerations.11 / 13[MS-DPSSAS] - v20170816SQL Server Analysis Services Data Portability OverviewCopyright 2017 Microsoft CorporationRelease: August 16, 2017
3Change TrackingThis section identifies changes that were made to this document since the last release. Changes areclassified as Major, Minor, or None.The revision class Major means that the technical content in the document was significantly revised.Major changes affect protocol interoperability or implementation. Examples of major changes are: A document revision that incorporates changes to interoperability requirements.A document revision that captures changes to protocol functionality.The revision class Minor means that the meaning of the technical content was clarified. Minor changesdo not affect protocol interoperability or implementation. Examples of minor changes are updates toclarify ambiguity at the sentence, paragraph, or table level.The revision class None means that no new technical changes were introduced. Minor editorial andformatting changes may have been made, but the relevant technical content is identical to the lastreleased version.The changes made to this document are listed in the following table. For more information, pleasecontact ass2.1.3.7 AdditionalConsiderationsDefined the "secrets" properties of the DataSourcecomplex type.major12 / 13[MS-DPSSAS] - v20170816SQL Server Analysis Services Data Portability OverviewCopyright 2017 Microsoft CorporationRelease: August 16, 2017
4IndexAAnalysis Management Objects (AMO) 9Writebackcube and partition 10dimension 10Writeback data - exporting 9CChange tracking 12Cube and partition writeback 10DData mining 8Data PortabilityAnalysis Management Objects (AMO) 9cube and partition writeback 10data mining 8dimension writeback 10exporting metadata 6exporting writeback data 9format and protocol summary (section 2.1.2 8,section 2.2.2 10)methodology 10Microsoft SQL Server Management Studio 8scenarios 6Unified Dimensional Model (UDM) 6Dimension writeback 10FFormat and protocol summary (section 2.1.2 8,section 2.2.2 10)GGlossary 4IInformative references 5Introduction 4MMetadata - exporting 6Microsoft SQL Server Management Studio 8RReferences 5TTracking changes 12UUnified Dimensional Model (UDM) 6W13 / 13[MS-DPSSAS] - v20170816SQL Server Analysis Services Data Portability OverviewCopyright 2017 Microsoft CorporationRelease: August 16, 2017
The SQL Server Analysis Services Data Portability Overview document provides an overview of data portability scenarios between SQL Server Analysis Services and a vendor's application. Analysis Services provides a business intelligence (BI) platform that enables end users and IT professionals to efficiently analyze business data.
![[MS-DPSSAS]: SQL Server Analysis Services Data Portability .](/img/15/5bms-dpssas-5d-140520.jpg)
