SQL Server Analysis Services (SSAS) OLAP Data Integration with Office 365

Microsoft SQL Server Analysis Services (SSAS) OLAP cube query data can be integrated and synchronized codeless with 150+ external systems, on premises or in the cloud, using the Layer2 Cloud Connector. For example you can push the data to Microsoft Office 365 and SharePoint to share reports, access data mobile, add change notifications and start workflows for better collaboration. You will find more supported systems and applications here.​​​​​

SQL Server Analysis Services (SSAS) is a technology from the Microsoft BI stack, to develop Online Analytical Processing (OLAP) solutions. In simple terms, you can use SSAS to create cubes using data from data marts / data warehouse for deeper and faster data analysis. Using the Layer2 Cloud Connector you can do both, push external data from 150+ sources into SQL to build cubes, but also query the cubes to retrieve results for better reporting in external systems like SharePoint, Office 365, or Dynamics.

Benefits of SSAS Data Integration with SharePoint, Office 365 and 150+

Microsoft SQL Server Analysis Services (SSAS) data integration via Layer2 Cloud Connector has the following features and benefits:

• Connect and sync your local or cloud-based SSAS data with 150+ other data sources, e.g. native SharePoint lists in Office 365 or on-premises, keeping them always up-to-date.
• High speed of implementation: No programming or tools required. Predefined sample connection templates included. Setup in a few minutes guided by wizards. Remote setup support (30 minutes are free).
• Low risk in project: No installation or customizing for the SQL Server / SSAS data source and the data destination required. Just install the connector on any computer in your network, in the cloud, or even your laptop.
• Highest Security: Connection via secure web services (SSL). Disconnected systems: A connection is only required for data exchange – not for list or library usage. Users do not have direct access to external corporate data sources. You don't have to open your network for the Internet. The application runs where your data resides today - on a server or client inside your corporate network.
• Highest performance: Can manage very large SQL databases, SSAS OLAP Cubes, SharePoint lists and libraries. No SharePoint 5,000 items limit. Can scale-out to multiple servers or to the Microsoft Azure Cloud, if required. No bulk-import as with SSIS, data changes are processed only.
• Fully compatible with 3rd-party data providers: Data providers of the application vendors (e.g. Oracle, mySQL, IBM), 3rd-party data providers, or included Layer2 data providers can be used to access 150+ data sources.

• Virtually any data sources supported: ODBC, OLEDB, OData, SOAP, Microsoft .NET based providers, Files (Excel, XML, CSV, Text), RSS / XML feeds, SQL databases like MS SQL, SQL Azure, Oracle, MySQL, IBM DB2, IBM AS/400, IBM Informix; Notes, SharePoint, Office 365 (lists and libraries), local file system, cloud-based file stores, Exchange, Active Directory, Dynamics NAV/CRM, Navision, SAP, and many more. More complex data sources, REST-based web services, custom COM, or .NET objects can be connected using 3rd-party add-ons. Cloud-to-cloud connections, e.g. to salesforce.com, Twitter, Facebook, Google, Amazon, BOX.NET and others are also supported.
• Layer2 CSOM Data Provider included: You can connect and sync your SSAS data with SharePoint lists and libraries on-premise or in the Office 365 / OneDrive for Business cloud.

• Layer2 File System Data Provider included: You can sync local files or file server shares with SharePoint Online or on-premises libraries, e.g. for backup, collaboration or mobile access. Optionally, you can sync metadata only - not the file itself. You can link to, for example, your own FTP/HTTP, to Azure, BOX, or SkyDrive / OneDrive to save space or use low-cost Office 365 plans. You can update file metadata based on SQL Server queries.

• Layer2 OData Data Provider included: You can connect your SSAS data to all OData sources, e.g. Microsoft CRM/ERP or SAP via NetWeaver.
• Layer2 XML Data Provider included: You can connect your SSAS data to any XML-based files and web requests, and filter results by using XPath query language.

• Layer2 SOAP Web Services Provider: Connect SQL to to SOAP-based web services codeless with flexible parameters.
• Very flexible sync options: Uni- and bi-directional sync are supported to SQL, one-way from SSAS Cubes to external systems. Execute a specific connection scheduled in background (Windows Service) or on demand (e.g. by command line). Sync all data or pre-selected subsets only. Select source and target folders, make use of additional no-delete, no-insert, no-update options.
• Data type conversion included: Sync SAAS cube data with SharePoint lists for better reporting and collaboration.
• Process integration: Raise change notifications and start workflows in the cloud, triggered on external SSAS data changes. Find your SSAS data changes in Delve via Office Graph. All features of native SharePoint lists and libraries are supported, e.g. views, sorting, filtering, grouping, search, mobile and offline access, social features like tagging and enterprise metadata, RSS feeds, change notifications per email, or workflows in the cloud on external data change. You can also add your own business logic.
• Mobile and offline access: You can sync SSAS Server queries with Office 365 / SharePoint Online for data access from everywhere, with any device, at any time - also offline with write-back of changes.
• Additional unmapped "SharePoint-only" columns can be created: Ranking, rating, managed metadata, comments or file attachments.
• Data aggregation: Multiple connections can combine several different SSAS data queries into one list.
• Flexible logging and alerts: You can log to files, databases, and Windows Event Log via NLOG.
• Free Shareware Edition: You can test the free Shareware Edition as long as you want or you can use in production with limited features. You can upgrade an evaluation / Shareware version to a licensed version at any time without new installation.
• Flexible, cost-effective licensing: Personal or Professional Edition, depending on requirements. Licensing per installation (no user CALs, not bound to any SharePoint server or site, just a one-time fee - no data volume-related costs). Easy to setup and maintain. You can save SQL Server user CAL license costs by providing the data in external systems - as a synchronized copy.

SSAS Specific Configuration Settings

​As a first step you have to prepare your OLAP cube and data query in SSAS, for example as shown below.

In the Layer2 Cloud Connector the data source entity must be configured as follows to connect to SSAS.

Fig.: SSAS sample query and connection in the Layer2 Layer2 Cloud Connector

When the connection is established you can preview the cube data in the Connection Manager and goto the Data Mapping section:

Fig.: Field / Column mapping for source and destination system 

After successful mapping you can sync manually the first time, and automatically scheduled in background later on. In case of SharePoint / Office 365 as target system your data could look like this:

Fig.: List-based SharePoint reporting directly from an SSAS OLAPcube. You can make use of all list features to customize your report, trigger notifications and workflows, if required. You can also add some graphics / diagramms.

Please note the following about connection settings, features and possible issues.

• Please select the OLEDB data provider from the list of installed providers. The MS OLAP OLEDB Data Provider must be installed. It comes with the SSAS package.
• You can make use of any connection strings for SSAS as usual, e.g.:
  Data Source=localhost; Provider=MSOLAP.5; Cube=Adventure Works; Initial Catalog=AdventureWorksDW2014Multidimensional-SE
  More about SSAS connection strings here.
• In case of access rights issues take a look below (known issues).
• You can make use of all queries your data provider supports. For example:
  Select
  non empty CrossJoin([Product].[Category].children,[Product].[Subcategory].children,
  [Product].[Product].children) on rows,
  {[Measures].[Internet Sales Amount],[Measures].[Internet Freight Cost]} on columns
  from [Adventure Works]
  where {[Date].[Calendar Year].[CY 2013]};
  You will find a great intro to OLAP queries here.
• Primary key for data sync must be set to any column(s) having unique values (together). In this case:
  [Product].[Category].[Category].[MEMBER_CAPTION], [Product].[Subcategory].[Subcategory].[MEMBER_CAPTION], [Product].[Product].[Product].[MEMBER_CAPTION]​
• Bi-directional sync is not supported. The connection is for reading data from the SSAS OLAP cube and write to any other system, e.g. SharePoint, Office 365 etc.

Do you have any issues to connect? Please contact [email protected] for next steps.

Possible Use Cases For SSAS Data Integration

​You will find more information about specific scenarios here:

• Connect SSAS data with SharePoint on-prem or Online to better share reports, access data mobile, alert users via notifications under specific conditions, use workflows to add business logic.
• Connect and sync Microsoft SQL Server SSAS data queries with Dynamics CRM or ERP via OData
• Push SQL Server data to Microsoft Exchange for mobile and offline access on any device (BYOD)

Can't find the scenario you are looking for? Please contact [email protected] for help.

SQL Server Analysis Services Integration - Known Issues and Workarounds

• In case of integrated authentication (trusted connection) please make sure that the current user has appropriate access rights. Also take care about access rights of the Layer2 Cloud Connector Windows Service. For access from other servers and impersonation possibly Kerberos is required.

 Ready to go next steps?