How to effectively extract data from an OLAP cube by relying upon T SQL
How to effectively extract data from an OLAP cube by relying upon T-SQL
Steve has presented papers at 8 PASS Summits and one at PASS Europe 2009 and 2010. He has recently presented a Master Data Services presentation at the PASS Amsterdam Rally.
Steve has presented 5 papers at the Information Builders' Summits. He is a PASS regional mentor.
View all posts by Steve Simon Latest posts by Steve Simon (see all) Reporting in SQL Server – Using calculated Expressions within reports - December 19, 2016 How to use Expressions within SQL Server Reporting Services to create efficient reports - December 9, 2016 How to use SQL Server Data Quality Services to ensure the correct aggregation of data - November 9, 2016
SQLShack
SQL Server training EspañolHow to effectively extract data from an OLAP cube by relying upon T-SQL
October 19, 2016 by Steve SimonIntroduction
Last month I ran two Business Intelligence pre-conferences in South Africa. A interesting request arose during the course of the preconference in Cape Town. The individual wanted an approach to extracting data from an OLAP cube that would avoid intensive utilization of MDX and more reliance upon T-SQL. His main concern was with filtering the data at run time, via the report front end. In this “fire side chat” we shall do just that, utilizing the cube that comes with the new Microsoft database “WideWorldImporters” and we shall learn how we can get from this 123456789 SELECT NON EMPTY { [Measures].[Quantity - Sale], [Measures].[Unit Price - Sale], [Measures].[Ext Revenue], [Measures].[WWI Invoice ID] } ON COLUMNS , NON EMPTY { ([Customer].[Customer].[Customer].ALLMEMBERS * [City].[City].[City].ALLMEMBERS * [Invoice Date].[Date].[Date].ALLMEMBERS * [City].[State Province].[State Province].ALLMEMBERS ) } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM [Wide World Importers DW] CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS to this!Preparitory work
Should you not have a copy of the WideWorldImportersDW, please do download a copy of the relational database. The relational database may be found at this link Simply restore the database to your desired SQL Server 2016 instance. We are now ready to begin.Getting started
Opening Visual Studio 2015 or the latest version of SQL Server Data Tools for SQL Server 2016, we create a new Analysis Services project as shown below. Opening Visual Studio of SQL Server Data Tools, we select “New” and “Project” (see above). We are asked if we wish to delete the group and its related rows and columns OR merely to delete the grouping itself. We select “Delete group only” (see above). We do however wish to set the “Row Grouping” as we shall want to view our data as a summation of revenue by invoice number. We click upon the “Group Properties” tab. In the screen shot above, we set the grouping to be based upon invoice number. Our next task is to add the fields from the stored procedure to our matrix. This is shown above. We have discussed this process in detail in numerous past “chats”. Our report construction is now complete.Let us give it a whirl
Clicking the “Preview” tab, we select the “Calendar” control and set the start date to 1/1/2013 (see above). In a smilar fashion we set the end date to 1/5/2013 (see above). We now click “View Report”. We note that our data is now visible to the user and that our report is now complete. Naturally the reader will want to sort and format the data. We have covered this as well in numerous past “get togethers” however the screen dump below shows our data sorted by invoice number and the numeric values have been rounded to the nearest dollar (see below). In order to polish up the data it is necessary to convert a few of the fields to numeric values. This is most easily achieved within the stored procedure itself. I have included the final code sample in Addenda 3 (below).Conclusions
Oft times we are all faced with the delema of having to work with MDX. Filtering the data via MDX is a challenge even at the best of times. This is especially true when predicates are complex and change periodically. Through the usage of a small piece of MDX code within a subquery, we are able to pull the necessary data and efficiently and effectively filter it via T-SQL; if we utilize a linked server and the OpenQuery function. So we come to the end of another “fire side chat”. As always, should have any questions, please do feel free to contact me. In the interim ‘Happy programming’.Addenda 1 OLAP Query
123 SELECT NON EMPTY { [Measures].[WWI Invoice ID], [Measures].[Quantity - Sale], [Measures].[Unit Price - Sale], [Measures].[Ext Revenue] } ON COLUMNS, NON EMPTY { ([Date].[Date].[Date].ALLMEMBERS * [City].[City].[City].ALLMEMBERS * [Customer].[Customer].[Customer].ALLMEMBERS ) } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM [Wide World Importers DW] CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGSAddenda 2 Creating a linked server
12345678910111213141516171819202122 --Set up a linked server--http://social.msdn.microsoft.com/Forums/en-US/sqldatamining/thread/a786a569-9dc0-4ea1-929f-4e48a5602b96USE masterGOEXEC sp_addlinkedserver @server='WWI2', -- local SQL name given to the linked server @srvproduct='', -- not used @provider='MSOLAP', -- OLE DB provider @datasrc='STR-SIMON\Steve2016b', -- analysis server name (machine name) @catalog='SQLShackOLAPMadeEasy' -- default catalog/database --Drop the server-- Clean-up--USE [master]--GO--EXEC master.dbo.sp_dropserver @server = WWI2 --GOAddenda 3 Our final Linked Server Query
1234567891011121314151617181920212223242526272829303132333435 Use SQLShackgoAlter procedure SQLShackLinkedQuery(@startdate as date,@enddate as date)as--declare @startdate as date--declare @enddate as date--Set @startdate ='20130101'--Set @enddate = '20130105'Select [[Customer]].[Customer]].[Customer]].[MEMBER_CAPTION]]] as Customer , [[City]].[City]].[City]].[MEMBER_CAPTION]]] as city , convert(date,(convert(varchar(15),[[Invoice Date]].[Date]].[Date]].[MEMBER_CAPTION]]]))) as Datee , [[City]].[State Province]].[State Province]].[MEMBER_CAPTION]]] AS State , [[Measures]].[Quantity - Sale]]] as Quantity, Convert(Decimal(38,2),Convert(varchar(20),[[Measures]].[Unit Price - Sale]]])) as Price , Convert(Decimal(38,2),Convert(varchar(20),[[Measures]].[Ext Revenue]]])) as Revenue , Convert(int,Convert(varchar(20),[[Measures]].[WWI Invoice ID]]])) as InvoiceNO from Openquery (WWI2,'SELECT NON EMPTY { [Measures].[Quantity - Sale], [Measures].[Unit Price - Sale], [Measures].[Ext Revenue], [Measures].[WWI Invoice ID] } ON COLUMNS , NON EMPTY { ([Customer].[Customer].[Customer].ALLMEMBERS * [City].[City].[City].ALLMEMBERS * [Invoice Date].[Date].[Date].ALLMEMBERS * [City].[State Province].[State Province].ALLMEMBERS ) } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM [Wide World Importers DW] CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS')rwhere convert(date, Convert(varchar(15),[[Invoice Date]].[Date]].[Date]].[MEMBER_CAPTION]]] )) between @startdate and @enddate order by InvoiceNOReferences
OPENQUERY (Transact-SQL) Create Linked Servers (SQL Server Database Engine) Formatting Numbers and Dates (Report Builder and SSRS) Author Recent Posts Steve SimonSteve Simon is a SQL Server MVP and a senior BI Development Engineer with Atrion Networking. He has been involved with database design and analysis for over 29 years.Steve has presented papers at 8 PASS Summits and one at PASS Europe 2009 and 2010. He has recently presented a Master Data Services presentation at the PASS Amsterdam Rally.
Steve has presented 5 papers at the Information Builders' Summits. He is a PASS regional mentor.
View all posts by Steve Simon Latest posts by Steve Simon (see all) Reporting in SQL Server – Using calculated Expressions within reports - December 19, 2016 How to use Expressions within SQL Server Reporting Services to create efficient reports - December 9, 2016 How to use SQL Server Data Quality Services to ensure the correct aggregation of data - November 9, 2016