Adatis

Adatis BI Blogs

Process an Azure Analysis Services Tabular Model from an Azure Function

A couple of weeks ago I wrote a blog post detailing how to process a tabular model from a stored procedure. The challenge there was to have a way of processing a tabular model on demand from a web application. The challenge still exists, but how do you achieve the same in Azure using Platform as a Service (PaaS) objects which do not have the benefit of a full server and items, such as msdb and SQL Agent, to aid in the process?In this post, I’m going to show you how to process a tabular model only using Azure PaaS offerings. Not only am I going to show you how to do process a tabular model on-demand, but also how to process a tabular model on a schedule. This post has taken inspiration and part of the code base from the a Microsoft blog: Automating Azure Analysis Services processing with Azure Functions.Azure FunctionsBefore we begin properly, it’s worth spending some time introducing Azure Functions. According to Microsoft, Azure Functions are:…a solution for easily running small pieces of code, or "functions," in the cloud. You can write just the code you need for the problem at hand, without worrying about a whole application or the infrastructure to run it. Functions can make development even more productive, and you can use your development language of choice, such as C#, F#, Node.js, Java, or PHP. Pay only for the time your code runs and trust Azure to scale as needed. Azure Functions lets you develop server less applications on Microsoft Azure.They are super useful for extending the capabilities of any solution and not just limited to what we’re going to cover here.On-Demand RefreshThe use of Azure Functions creates a trigger for the on-demand refresh of a tabular model from the web application or web hook, this is to make sure that selected elements of data in a tabular model, for example hot partitions, are always up to date. The following describes the process that Azure Functions will be involved in this scenario:The steps that are needed to create an Azure Function for On-Demand Refresh are as follow:1) Create an Azure Function AppNavigate to the Azure Portal and create a Function App (the name changes quite a bit, so don’t be concerned if it’s not exactly displayed as it is below)2) Create a new FunctionAfter you’ve created the Function App, we need to add a new Webhook + API function, which we’ll use as the basis for our on-demand refresh. Click on the + button next to Functions, select Webhook + API, choose C# as your language and click Create this function.3) Configure the FunctionDownload the latest client libraries for Analysis Services. This needs to be done to your local machine so you can then copy these files to your Azure Function App. After you’ve downloaded the client libraries, the DLLs can be found in C:\Program Files (x86)\Microsoft SQL Server\140\SDK\Assemblies. The two files you need are:C:\Program Files (x86)\Microsoft SQL Server\140\SDK\Assemblies\Microsoft.AnalysisServices.Core.DLLC:\Program Files (x86)\Microsoft SQL Server\140\SDK\Assemblies\Microsoft.AnalysisServices.Tabular.DLLThe documentation references the 130 assemblies, which is not correct and will not work. You need the assemblies in 140.In order to add these assemblies to your function, you’ll need to add a folder called “bin”. To do this, select your Function App, click Platform features, and under Development Tools, click Advanced tools (Kudu).In Kudu, click Debug console and select Cmd. Navigate to the site\wwwroot\OnDemandHttpTrigger folder and add the “bin” folder here by clicking the + button.Once you’ve added the “bin” folder, go back over to the Azure portal and select your function OnDemandHttpTrigger.On the right under View files, navigate to the bin folder. Then click the Upload button to add the two previously mentioned DLLs to the bin folder.You should see the two DLLs in your bin folder now.4) Add Azure Analysis Services Connection String to the Function AppThis step is going to add the connection string to the Azure Analysis Services (AAS) service to the entire Function App, not just individual functions. Click the name of your Function App, then select Platform features. Select Application settings under General Settings.Now we need to add our connection string under the Connection strings section. You’ll need your AAS server name and a user ID and password that has access to the AAS database.You can find your AAS server name by navigating to your AAS database in the Azure portal and copying the value constructed after clicking Show server connection strings:Your connection string should look like this:Provider=MSOLAP;Data Source=<your aas server>; Initial Catalog=<aas database name>;User ID=<your username>;Password=<your password>Back in the screen for the Function App, fill in the Name textbox with a name for your connection string and paste your connection string in the Value text box:Click Save at the top to save these settings for your Functions.5) Time for CodeOur Function App has been configured, now we need to add code to the function. The function comes with a working function, for which to test out the functionality, but we don’t need everything that is on offer.We’re going to programmatically process the tabular model. In doing so, we’ll leverage Analysis Services Management Objects (AMO). If you’re new to AMO, start here.Paste in the following code (all code referenced can also be download from my GitHub Repo):#r "Microsoft.AnalysisServices.Tabular.DLL"#r "Microsoft.AnalysisServices.Core.DLL"#r "System.Configuration"using System;using System.Configuration;using Microsoft.AnalysisServices.Tabular;using System.Net;public static async Task<HttpResponseMessage> Run(HttpRequestMessage req, TraceWriter log){log.Info("C# HTTP trigger function processed a request.");// parse query parameterstring status = req.GetQueryNameValuePairs().FirstOrDefault(q => string.Compare(q.Key, "status", true) == 0).Value;if (status == null){// Get request bodydynamic data = await req.Content.ReadAsAsync<object>();status = data?.status;}if (status == "execute"){log.Info($"C# trigger function started at: {DateTime.Now}");  try            {Microsoft.AnalysisServices.Tabular.Server asSrv = new Microsoft.AnalysisServices.Tabular.Server();var connStr = ConfigurationManager.ConnectionStrings["AASTabular"].ConnectionString;asSrv.Connect(connStr);Database db = asSrv.Databases["azureadventureworks"];Model m = db.Model;//db.Model.RequestRefresh(RefreshType.Full);     // Mark the model for refreshm.RequestRefresh(RefreshType.Full);     // Mark the model for refresh//m.Tables["Date"].RequestRefresh(RefreshType.Full);     // Mark only one table for refreshdb.Model.SaveChanges();     //commit  which will execute the refreshasSrv.Disconnect();            }catch (Exception e)            {log.Info($"C# trigger function exception: {e.ToString()}");            }log.Info($"C# trigger function finished at: {DateTime.Now}"); }return status == "execute"?req.CreateResponse(HttpStatusCode.OK, "Successfully Processed Tabular Model ") :req.CreateResponse(HttpStatusCode.BadRequest, "Please pass a status on the query string or in the request body");}Click the Save button at the top.6) Test, Test, TestClick the Run button at the top to test the function The function can also be tested in a web browser, and be called by a Web App using the POST HTTP method.Now we have a fully functioning method of refreshing an Azure Analysis Services tabular model on-demand.Scheduled RefreshThe use of Azure Functions creates a trigger for the scheduled refresh of a tabular model, this is to make sure that the entire tabular model has the latest data and is always up to date. The following describes the process that Azure Functions will be involved in this scenario:The steps that are needed to create an Azure Function for Scheduled Refresh are as follow:1) Create a FunctionWe’ve created our Function App, and now we need to add a new Timer Trigger function, which we’ll use as the basis for our scheduled refresh. Click on the + button next to Functions, select Timer, choose C# as your language and click Create this function.2) Configure the TimerWhat use is a timer without a schedule? To give the timer a schedule, click Integrate, set the schedule and click Save.The Schedule text box expects a CRON expression to define the days and times that the function should execute. Click the little Documentation button on the screen above to read about CRON expressions. The schedule I’ve set is to run once everyday at 09:30AM.3) Configure the FunctionSee step 3 of the On-Demand Function for detailed steps. You’ll need to create the bin folder and upload the DLLs to the bin folder.4) Time for CodeWe’ve configured our function, so now it’s time to add the code. The code base is much simpler than the On-Demand code base, mainly because it’s doing fewer tasks. But the AMO section is exactly the same. Paste in the following code:#r "Microsoft.AnalysisServices.Tabular.DLL"#r "Microsoft.AnalysisServices.Core.DLL"#r "System.Configuration"using System;using System.Configuration;using Microsoft.AnalysisServices.Tabular;public static void Run(TimerInfo myTimer, TraceWriter log){log.Info($"C# Timer trigger function started at: {DateTime.Now}");  try            {Microsoft.AnalysisServices.Tabular.Server asSrv = new Microsoft.AnalysisServices.Tabular.Server();var connStr = ConfigurationManager.ConnectionStrings["AASTabular"].ConnectionString;asSrv.Connect(connStr);Database db = asSrv.Databases["azureadventureworks"];Model m = db.Model;//db.Model.RequestRefresh(RefreshType.Full);     // Mark the model for refreshm.RequestRefresh(RefreshType.Full);     // Mark the model for refresh//m.Tables["Date"].RequestRefresh(RefreshType.Full);     // Mark only one table for refreshdb.Model.SaveChanges();     //commit  which will execute the refreshasSrv.Disconnect();            }catch (Exception e)            {log.Info($"C# Timer trigger function exception: {e.ToString()}");            }log.Info($"C# Timer trigger function finished at: {DateTime.Now}"); }Click the save button at the top.5) Test, Test, TestClick the Run button at the top to test the function Now we have a fully functioning method of refreshing an Azure Analysis Services tabular model on-demand.ConclusionI have shown you how simple it is to invoke two methods of refreshing a tabular model using Azure Functions: an On-Demand refresh and a refresh by Schedule. I hope that you take inspiration and use these methods in your use of both Azure Analysis Services and Azure Functions.

Process a Tabular model from a Stored Procedure

The ChallengeRecently, at a client, I was challenged to create a stored procedure that would process a tabular model. This stored procedure would then be executed from a web application. The process behind it being: a user enters data into a web application, which gets written to a database. That data then needs to be immediately surfaced up into reports, with additional calculations and measures along the way. Therefore the tabular model, which does all the additional calculation and measures, needs to be processed by a user from the web application. That’s the challenge – process a tabular model quickly, but should be processed by users on-demand.The SolutionPart one: Method of processThere’s quite a few methods to process a tabular model: use an SSIS package, PowerShell, SQL Agent Job and others. I opted for SQL Agent Job because it was the most simple method of execution without having to reconfigure the server or introduce technologies and languages that weren’t already in use. First things first, create a SQL Agent Job, I called mine ProcessTabular. Then create a Step. The Type should be SQL Server Analysis Services Command, input the server address and input the refresh script. I’m using SQL Server 2016, so using Tabular Model Scripting Language (TMSL) for my command. XMLA commands also work for older versions. A full list of commands for processing a tabular database can be found here. Part two: Start the Agent JobNow that we have a SQL Agent job, we’ll need to start that job using SQL. Luckily, there’s a system stored procedure that can start agent jobs: msdb.dbo.sp_start_job Method for calling it is EXEC msdb.dbo.sp_start_job 'ProcessTabular'producing the following successful messagePart three: The Stored Proceduresp_start_job works, but it doesn’t accommodate for providing a completion message, or informing a user that a process is in progress. Introducing the code for the stored procedure:CREATE PROCEDURE [dbo].[ProcessTabularDatabase]  AS  DECLARE @JobName nvarchar(50) = 'ProcessTabular', @ResultCheck INT  IF NOT EXISTS(              SELECT 1          FROM msdb.dbo.sysjobs_view job           JOIN msdb.dbo.sysjobactivity activity ON job.job_id = activity.job_id          WHERE               activity.run_Requested_date IS NOT NULL           AND activity.stop_execution_date IS NULL           AND job.name = @JobName          ) BEGIN           PRINT 'Starting job ''' + @JobName + '''';      EXEC msdb.dbo.sp_start_job @JobName;     WHILE (1 = 1)  BEGIN   SELECT @ResultCheck =  IIF(stop_execution_date IS NULL,1,0)       FROM msdb.dbo.sysjobactivity AS sja      JOIN msdb.dbo.sysjobs AS sj ON sja.job_id = sj.job_id      WHERE sj.name = @JobName    IF @ResultCheck = 0 BREAK;  END    PRINT 'Successfully Processed Tabular Database' END ELSE BEGIN      PRINT 'Job ''' + @JobName + ''' is already started '; ENDConclusionThis stored procedure can be executed by the web application, enabling users to process a tabular database on-demand and get feedback as to the success of the task.

Working with Manual and Automated KPIs in a Tabular Cube

In a recent project, we had to produce a scorecard using a set of manual and calculated KPIs. To obtain the manual KPI figures, we used Master Data Services (MDS) where the users could insert the values, while for the calculated, we used the base measures created in the tabular cube. So far, this requirement does not look very complicated, however, what if I tell you that the same KPI can either be manually or automatically calculated by the cube? And that we have to present the values for different levels of a hierarchy? And that some of the KPIs are not absolute values but ratios? Now that I got your attention, let’s have a look at the solution we implemented.How to join manual and automated KPIs?Because the client couldn’t always provide the data to calculate the base measures, we delivered an MDS model to, among other functionalities, manually insert the numeric values. You can check this blog if you want to know more about the Survey model (http://blogs.adatis.co.uk/tristanrobinson/post/Modelling-Survey-Style-Data).Since we were working with different markets, the same KPI could either be manually or automatically calculated, which means, the cube had to to select the appropriate scenario, depending on the selected market. In order to achieve such requirement, we created 3 measures.AutKPI – Using base measures from multiple tables, we defined the DAX code to calculate the KPIManKPI – Knowing all the MDS values were in one table, we defined a simple DAX query to sum the valuesActual – This measure was implemented with an IF statement. Eg.         Actual:=IF(ISBLANK([AutKPI]), [ManKPI], [AutKPI])How to aggregate ratio KPIs?Let’s have a look at the example below, where we are calculating the KPI for two levels of a geography hierarchy.Automated KPI 1 – Europe is naturally aggregating the values from Great Britain and IrelandAutomated KPI 2 - Considering we are using base measures, the cube can properly calculate the KPI at Europe level. Manual KPI 1 – All manual entries were aggregated with a SUM. Because those are absolute values, the figure for Europe is correctManual KPI 2 ­– Following the same logic as Manual KPI 1, we can see the Europe value is incorrect. Because this is a ratio we can't aggregate the value from the lower levels. The simplest approach to resolve this problem was to create a new calculation using an AVERAGE function, however, considering the requirements, we had to introduce a weighted average.Implementing weighted averagesThe first step to this approach is to define a weight for each market. Since the values can change according to the user’s needs, we added a new entity to the MDS model.Now let’s consider the example below showing the weighted approach.Following is the formula to calculate the KPI at Europe level. For a better understanding, I split it in different steps.C1 GB: Manual KPI x WeightC1 Ireland: Manual KPI x WeightC2: C1 GB + C1 IrelandC3: GB Weight + Ireland WeightEurope KPI: C2 / C3The scope of the project stated we had to implement the following logic:· When presenting the KPIs at market level, don’t apply the weighting· When presenting the KPIs at region level, apply the weighting but only for the ratio KPIsThe biggest challenge of this requirement was to overwrite the aggregating logic of the geography hierarchy. To achieve that, we implemented a dynamic segmentation pattern on the ratio KPIs (more details on this link http://www.daxpatterns.com/dynamic-segmentation/). This approach can be split in four steps.First step is the calculation of our numerator.Num Weighted AutKPI – Because the base measures from our automated KPIs are from different tables, we had to firstly group our data by market and region level and only then apply the calculation. Eg.Num Weighted AutKPI:=                  CALCULATE(                    SUMX(                       SUMMARIZE(                          'KPI Value',                           Market[Region],                           Market[Market]                           ),                        [AutKPI] * [KPI Weight]                    )                 ) Num Weighted ManKPI – On this instance, the grouping was not necessary because we only had one measure to consider. Eg.Num Weighted ManKPI:=                  CALCULATE(                    SUMX(                       'KPI Value',                        CALCULATE(                           SUM ( 'KPI Value'[KPIActual] ) * [KPI Weight]),                           'KPI'[KPI] = "Manual KPI"                           )                        )                    ) The second step is the calculation of our denominator.Den Weighted AutKPI – Once again, because the weights were stored in a single table no grouping was necessary.Den Weighted AutKPI:=                  CALCULATE(                    SUMX(                       'KPI Value',                        CALCULATE([KPI Weight])                        )                    ,'KPI'[KPI] = “Automated KPI"                 ) Den Weighted ManKPI – The same logic applies on this instance.Den Weighted ManKPI:=                  CALCULATE(                    SUMX(                       'KPI Value',                        CALCULATE([KPI Weight])                        )                    ,'KPI'[KPI] = “Manual KPI"                 ) The third step is the division of our measures.Weighted AutKPI:= DIVIDE([Num Weighted AutKPI], [Den Weighted AutKPI]) Weighted ManKPI:= DIVIDE([Num Weighted ManKPI], [Num Weighted ManKPI]) The fourth step is the calculation of our Weighted Actual measure, by once again, using an IF function.Weighted Actual:= IF(ISBLANK([Weighted AutKPI]), [Weighted ManKPI], [Weighted AutKPI])Finally, considering we only wanted to use the weighted measures for a subset of our KPIs, we created a new measure using a SWITCH function. Eg.Actuals:=       SWITCH(         VALUES(‘KPI’[KPI]),         “Percentage KPI”, [Weighted Actual],         "Percentage KPI2", [Weighted Actual],         “Absolute KPI”, [Actual],         "Absolute KPI2",[Actual]      ) Hopefully, I was able to clearly demonstrate our problem and how we managed to implement a solution to solve it. As always, if you have any questions or comments, do let me know.

SSAS Tabular Tables Losing Structure of Measures

What is the issue While recently working on a SQL Server 2012 Tabular cube in Visual Studio I came across an extremely frustrating issue where the visual layout of my measures was re-ordered. You are most probably aware that the actual structure or ordering of the measures within the workspace of a Tabular table in Visual Studio holds no relevance to how Visual Studio sees or uses the measures however, if you are like me then you will naturally organise your measures into some kind of pattern making them easier to use, maintain or locate. In this instance, I had all of my visible ‘switching’ measures listed in the first column and then their hidden, dependant measures which were different date analysis calculations listed in adjacent columns. For example, all of my ‘Year to Date’ calculations were in the second column, my ‘Month to Date’ calculations were in the third column, etc. Eventually I had over 20 visible switching measures in the first column, each with 8 separate hidden date analysis calculations giving me a total of 160 measures which were all neatly organised in the table’s workspace. An example of what this may look like is shown in the following diagram; At a later point I opened the table to add a new measure and to my surprise all of the existing measures had moved position and been restructured as shown in the following diagram; For the solution, we were using TFS as our source control and by going back through the version history of the SSAS Tabular project I found that a change to a completely different table which was checked in weeks earlier had the changes to this table’s layout checked in with it. Unfortunately, as there had been several other changes and check ins since this change, rolling back wasn’t an option. Here I had two options, painstakingly cut and paste each measure back into its original location or leave the table in its new format and move on. Although I have a slight OCD side to me when it comes to things like this for times-sake of the project I left it how it was; as I mentioned earlier the structure of the measures on a table holds no real relevance in SQL Server 2012 or 2014, it just purely helps with management and maintenance of the groups of measures. A colleague has also run into this issue since and their table was re-structured differently but Visual Studio seems to either add an additional column or take an existing one away and restructure the measures accordingly. Potential solutions I am unaware to the exact reason for this happening and therefore can’t give a suitable way to structure your tables for prevention. There is only one way I found to correct this issue once it has occurred other than manually cutting and pasting your measures back to the way you want. Luckily one of our team had not worked on the project since the initial check-in of the project which had the restructured table and had not got the latest code of the project. I copied their Model.bim.layout file which is located in the project folder in File Explorer and replaced my version with it. After restarting Visual Studio and re-opening my Project I had found that my table structure was back to its original layout. The issue here is that we have multiple developers working on the same Tabular project but different child tables therefore simply going through every table to check it hasn’t been restructured before every check-in would be inefficient as a layout change could easily be missed. The solution that our development team adopted was to separately attach the Model.bim.layout file to the solution in a Solution Items folder and then have this stored in source control as shown in the diagram below; This file should then be used to replace the existing Model.bim.layout file in the developer’s local Tabular project whenever they start working on the Tabular project and then replace the file in the Solution Items after they have finished working on the Tabular project. Checking this file in at the same time as their changes to the Tabular project allows the file to have an independent version history and the separation of this file from the Tabular project prompts the developer to manually check the layout of the tables before checking in the Model.bim.layout file, hopefully adding an additional check to prevent this issue from happening. Although this extra step which is needed whenever changes to the Tabular project are made will add time to the check-in process it is in my opinion worth it to ensure the structure of your tables in a Tabular project are kept consistent and easily maintainable; especially when they start to get large and complex. I have only witnessed or heard of this happening in Visual Studio 2013 with Tabular projects in both SQL Server 2012 and SQL Server 2014. I am hoping that with the many improvements and fixes that Microsoft has released for SQL Server 2016 Tabular projects this will no longer happen but if you have witnessed this in SQL Server 2016 then please comment below and let me know. Also, if you know of a better way to prevent this issue from occurring or have more information on why it happens again, please comment below.

Setting Up The Power BI Analysis Services Connector

The Power BI Analysis Services Connector is used in order to expose a Tabular model to Power BI allowing end users to consume data from the model directly for building of reports and ad-hoc analysis. The setup of the connector is very straightforward however you will should bear the following in mind - Only Tabular models are supported with the connectors – you will not be able to use this to enable reporting from a multidimensional database. - The Analysis Services Connector performs best if it is hosted on the same server that hosts the Tabular model. - The speed of the internet connection between the server running the Analysis Server Connector and the Power BI service is crucial to performance. - You can’t run the Analysis Services Connector on a server also running either the Data Management Gateway or the Power BI Personal Gateway. Installation Steps 1. Download the connector from http://www.microsoft.com/en-us/download/details.aspx?id=45333 2. Run the Analysis Services Connector Setup Program on the machine hosting the Tabular model. 3. Once the installation has completed you will be given the option to launch the connector     4. Enter the login details required to connect to Power BI 5. If the details are correct you will see the below screen: 6. Enter the account details required to connect to the Tabular Instance – clearly this needs to be an account with access to read from the Tabular model. 7. Give the connection a suitable name and a friendly error message to be displayed to users in the case that the connection fails This should complete the wizard, the next step is to log onto the Power BI site – if all has gone well you should see the model as per the below.

New Features in SQL Server 2016 – Part 2: Analysis Services

This blog solely focuses on the new Analysis Services features of SQL Server 2016 CTP2.  For anyone who may missed it – click here to view my opening blog on the Database Engine. Although there have not been any major Analysis Services (SSAS) enhancements in CTP2, it is pleasing Microsoft are still looking to improve this part of the BI Stack.  The majority of them seem to be geared towards Tabular and DAX, although there are plans to release further Multidimensional functionality in CTP2 and beyond. There are five key enhancements for SSAS: 1.      Tabular Model Partitions (Tabular). 2.      Many 2 Many (Tabular). 3.      Analysis Services PowerPivot mode. 4.      New DAX Functions (Tabular). 5.      Enable/disable attribute hierarchies. Tabular Model Partitions SQL Server SSAS 2016 CTP2 includes new parallel processing functionality for tables with two or more partitions, increasing processing performance. There are no configuration settings for this.  More information on this feature is displayed below: 1.      IN SSAS Tabular, partitioning big data marts will help drastically when processing new data.  Instead of having to process all data, you can just partition the relevant ones. 2.      In order to create, manage, and process partitions in SQL Server Management Studio, you must have the appropriate Analysis Services permissions defined in a security role. 3.      Parallel processing is also available in SSAS.  This happens automatically, when processing a table with more than one partitions.  Although you can choose to independently process a partition if required. Many to Many Dimensions (Tabular) There isn’t too much to show or say here yet apart from the fact that this feature is FINALLY HERE!  Gone are the complications of workarounds (thanks to Marco Russo and Alberto Ferrari), we can now just use the Many to Many functionality in Tabular.  Multidimensional already has this feature, which is one of the reasons why developers have yet to buy in to Tabular.  Unfortunately, Microsoft have yet to reveal much detail on to how this feature will work.  I just hope it performs as well as the current workarounds. Analysis Services PowerPivot mode To install PowerPivot for SharePoint is now a lot easier.  If you already know how to configure SharePoint 2013, this will help you greatly.  Taken from the MSDN Microsoft website, you simply use the Install Wizard and do the following: 1.      Select ‘Analysis Services’ from the standard Instance Feature. 2.      Choose ‘Analysis Services server mode’ and configure administrators (screenshot below).   New DAX Functions There are many new DAX functions, which are all detailed on MSDN website – click here for further detail. I have picked out 5 functions that will be highly beneficial when creating an SSAS Tabular cube.  I am looking at this form a very BI focused background so I would recommend looking at all of the new functions to see if they benefit your needs. 1.      TOPn a.      This is not actually a new function but has been updated. b.     Now allows TRUE/FALSE/ASC/DESC to specify sorting direction. 2.      CALENDAR a.      Returns a table with a single column named “Date” that contains a common set of dates. The range of dates is from the specified start date to the specified end date. b.      See Similar – CALENDARAUTO 3.      MEDIAN a.      Very simple – returns the median numbers in a column. b.      See Similar – MEDIANX (uses an expression). 4.      NATURALINNERJOIN a.      Inner join of a table with another table. The tables are joined on common columns in the two tables. If the two tables have no common column names, an error is returned. b.      See similar – NATURALLEFTOUTERJOIN 5.      SUMMARIZECOLUMNS a.      Enables you to group data together and return a summary table. Enable/Disable Attribute Hierarchies Again, this functionality is tailored towards SSAS Tabular.  There is a new setting that ties back to the Tabular mode metadata 1.      ColumnUsage property a.      DAXUsage                                                     i.     Attribute hierarchies can only be used in measures. b.     UnrestrictedUsage                                                     i.     Can be set in the XMLA (in SSDT) or by using an ALTER statement in the Tabular model.                                                    ii.     An example taken from MSDN:        <Alter>   <ObjectDefinition...>     <Database>       <Dimensions>         <Dimension>           <Attributes>             <Attribute>                <ddl500:ColumnUsage value=”ddl500_500”>                DAXUsage | UnrestrictedUsage                </ddl500:ColumnUsage>   Multidimensional Features The full SQL Server 2016 release will include some key enhancement to Multidimensional SSAS.  These include: 1.      Netezza as a Data Source (Netezza Data Warehouse | IBM - NDM Technologies). 2.      General performance improvements. 3.      Unnatural hierarchies. 4.      Distinct counts. 5.      DBCC support. Checks the logical and physical integrity of objects in the specified database. 6.      Expose on-premises multidimensional cubes in the cloud with Power BI. I will provide further information on the improvements, as and when Microsoft announce them. References For more information on all of the new SSAS SQL Server 2016 features, the below resources/blogs are highly recommended. ·        Official Microsoft Page - https://msdn.microsoft.com/en-us/library/bb522628(v=sql.130).aspx ·        Brew your own Business Intelligence Blog – http://byobi.com/blog/2015/05/ssas-related-enhancements-in-sql-server-2016/ ·        Jorg Klein SQL Server Blog - http://sqlblog.com/blogs/jorg_klein/archive/2015/05/22/bi-on-your-terms-with-sql-server-2016.aspx

KPI’s in Power View and DAX Query for KPI’s

I was recently approached by a client where we have assisted in implementing a Microsoft Analysis Services tabular solution and a supporting management pack of SSRS reports.  They were asking about utilising some of the KPI’s defined in their tabular cube in the SSRS reports using DAX queries.  I was at a bit of a loss and most web help seemed to suggest it couldn’t be done: Blog comment from 2012 However, with a bit of time to investigate it appears Power View does now support Tabular KPI’s and as such a DAX query should be able to pull them out.  My KPI base measure is named “Highest Ranking” and I am able to view its value, status or goal in Power View.  A little work with every SQL developer’s best friend Profiler and I had what I was looking for.  It appears that when you create a KPI SSAS is defining some measures in the background which do some of the work of the MDX KPI functions. Therefore the following DAX query against my cycling Demo cube contains the expected results and could therefore be used in SSRS reporting. EVALUATE ADDCOLUMNS( VALUES('DimRider'[Name]) , "Highest_Ranking", 'FactRanking'[Highest Ranking], "Highest_Ranking_Goal", 'FactRanking'[_Highest Ranking Goal], "Highest_Ranking_Status", 'FactRanking'[_Highest Ranking Status] ) ORDER BY 'DimRider'[Name]   I also tested for the existence of other KPI functions such as Trend and Weight but these do not appear to be present.  It is also interesting that the use of a KPI over a measure does not change the measure name but just group it in the field list and as such there is no need for a value function. For more info on KPI’s in Power View there is additional documentation here.  I am currently unsure of the required versions for this functionality so if it is missing from your environment I would love to hear from you.

Analysis Services Dimension Processing Errors

I’ve seen a few data quality issues recently in a some client data warehouses/marts where I’ve been asked to carry out some Analysis Services work. These data quality issues have often led to the classic SSAS dimension processing error, namely: “Errors in the OLAP storage engine: A duplicate attribute key has been found when processing: Table: 'dbo_DimProduct', Column: 'SubCategory', Value: 'Other'. The attribute is 'Sub Category'” Normally this error is easy to track down, but there are a few scenarios that I’ve encountered where SSAS attribute properties actually make it harder for you to troubleshoot. Scenario 1 – Trimming When hitting the above error, the first thing to do would be to run a query to see where the duplicate attribute is. In my example, I’ve just produced a very simple dimension that’s loosely based on Adventure Works – here are the attribute relationships: Therefore the first place to start would be to write a query that checks if we have any Sub Categories that are duplicated across Categories, such as: SELECT SubCategory FROM DimProduct GROUP BY SubCategory HAVING COUNT(DISTINCT Category) > 1 In my case, this actually yields no results. So what’s happening here? We can’t find any duplicates in SQL, but SSAS thinks there are duplicates. Well….Analysis Services dimension attributes contain a property called Trimming, which, by default, will remove spaces at the end of an attribute key or name. Needless to say, a dimension shouldn’t actually contain leading or trailing spaces, but in this specific scenario, the ETL wasn’t as robust as it could be. In the actual example I encountered, there was a tab at the end of one of the attribute names. To visualise that in the DimProduct example that I’ve created, the highlighted attribute has a tab at the end: Scenario 2 – NullProcessing The next scenario I want to highlight is the same error, but a different dimension. Again I’m going to create a simple example to illustrate what actually happened. This time I’ve got a customer dimension table, as follows: The error is: A duplicate attribute key has been found when processing: Table: 'dbo_DimCustomer', Column: 'CustomerCountryKey', Value: '0'. The attribute is 'Customer Country' A quick query on the above data will reveal that a CustomerCountryKey of 0 doesn’t actually exist twice for the same country, so what’s happened here? Again its due to a dimension property, this time on the attribute key, called NullProcessing. As its default is Automatic, it means that Nulls will get converted to zero during dimension processing. As we already have a member with CustomerCountryKey of 0, then we get an error. ETL and Data Quality These sort of issues highlight just why handling data quality during the ETL is so important! For the first scenario, some basic trimming/cleaning can fix the issue before it hits Analysis Services. As for the last issue, this is partly due to data warehouse/mart design. Personally I would never allow a dimension attribute to be null, partly because you shouldn’t present null attributes to a user, but also due to the lack of control that can result as shown above. Therefore a bit of design work up front to ensure that you have a robust data model is essential. Interestingly the properties that I’ve mentioned don’t exist for tables in Analysis Services Tabular. Whereas I think there are a few SSAS MD features that need to make it over into the next version of Tabular, I can personally live without the two properties outlined above.

Tabular Analysis Services Perspectives and Measure Groups

I’ve always liked how Excel and other client tools deal with measure groups in a multi dimensional SSAS cube. Once you connect to the cube, you get a drop down in the Pivot Table fields pane that lets you choose which measure group you want. From there, you get a filtered list of measures and dimensions. For example, if I connect to the Adventure Works 2012 sample cube, I get the following: By picking Internet Orders, I will see only the measures dimensions that relate to Internet Orders, which provides me with a good way of navigating a large cube. Tabular Unfortunately though, if I connect to a Tabular model (e.g. the sample Adventure Works 2012 model that I got from codeplex), then if I click the same drop down, then I will see every table in the entire model, whether the table  is a dimension, a fact or something else. E.g. here for a tabular model than contains 3 fact tables, but 15 tables in total, then I see all 15 tables: This isn’t ideal in my opinion, as I’d much rather that users had the ability to quickly jump to an area of interest, which measure groups achieve fairly well. As I have 3 fact tables, I would expect to see those 3 fact tables/measure groups in the drop down. I did accept this as a quirk of tabular until I stumbled across something recently. If you connect to a tabular perspective, then Excel behaves exactly as Multi Dimensional does, i.e. it shows you only measure groups in the drop down. To illustrate I’ve added a new perspective to the model that contains all tables and I’ve called this perspective ‘Adventure Works’: Now I can connect to this new perspective via Excel. Remember the perspective contains all my tables, so it shouldn’t be any different than connecting the model itself: The perspective does give a different result though – now only the actual measure groups are displayed to me in the drop down, which is much more user friendly: Summary This is completely different to the way that perspectives work in multi dimensional. When making MD cubes, I wouldn’t always need perspectives, just because the measure groups provided a ‘natural’ way of the users picking the subset of the cube that was of interest to them. Now with tabular, it seems that using a perspective will actually improve the user experience.

Power View Default Field Set

Here's another quick tip when creating data models for use with Power View - Default Field Sets can be created in both Tabular SSAS and PowerPivot that give the user a shortcut for automatically adding multiple fields to a report. As an example, here's a screenshot of the Default Field Set in PowerPivot - this will tell Power view that the following fields should be automatically selected when the table is added to a Power View report: I'd seen this in the documentation a while back, but hadn't actually used it, nor could I see a way to select the table. In fact, although there's no checkbox, it's just a simple single click on the actual table name, as I’ve highlighted below: When you click on the table name Product above, you will now get the following fields automatically added to a table visualisation: So not exactly rocket science, but worth doing! Here's how to set up the Default Field set for PowerPivot and Tabular Analysis Services.

Working with Images in Power View

Power View includes several ways to create visualisations with images, ranging from a simple table, to the scrollable tile visualisation. In this post I’m going to explain a bit about the tile visualisation, showing how it can be used in Power View, before showing a couple of tips for working with images in both PowerPivot and Analysis Services Tabular models. Power View Tiles The Power View Tiles visualisation allows you to display a scrollable series of images, which, when clicked on, will display data of your choosing. The report below is an example of this: What’s nice about the View above is that the names of the products are automatically displayed underneath the images. This is a sensible place to display the product names, as it means the area below can focus on whatever product level data you want, in this case Revenue by Month for the selected product. This works fine in the sample models (in my case the Tailspin Toys model that I think came with CTP3) but it wont work in vanilla PowerPivot / Tabular models unless you configure a few model settings. Trying to reproduce the report above without these settings will give the following tile visualisation, minus the product names: PowerPivot Advanced Settings To address this in PowerPivot, you first of all need to switch to Advanced Mode, which you can do by selecting this option from the File menu in PowerPivot: This gives you the following advanced tab: Clicking on the highlighted Table Behaviour button in the ribbon gives you the following Table Behaviour window: Here you can “change the default behaviour for different visualisation types and default grouping behaviour in client tools for this table”. The following properties should be set in order to get the tiles working correctly: Row Identifier – Specifies a column that only contains unique values, which allows the column to be used as an internal grouping key in client tools. Default Label – Specifies which column gives a row level display name, e.g. the product name in a Product table. This is key, as when a row level image is used, this property specifies which value to display alongside the image in tiles and other image-based visualisation. Default Image – Specifies which column contains images representing the row level data, e.g. pictures of products. The full details for all these properties can be found here. Tabular Models Tabular Models contain the same properties, which can be edited when you open a tabular project in the new SQL Server Data Tools. The image below shows how the properties have been configured for the column in my model called Image: Back to Power View If we now build a tile visualisation using the image column, we will see that we get the names of the products returned along with the image. It’s also worth noting that the Image and Product Name columns have now have an icon next to them, indicating that the field exhibits “row level” behaviour: Finally, for some examples of what’s new in Power View SQL 2012 RC0 take a look at this post on the Reporting Services Team Blog.