SSIS – Dropping Partitions in SQL Server Analysis Services (SSAS) with XMLA and Analysis Services DDL
Below are the steps that we have integrated into SSAS using SSIS so that we can then drop our old SSAS Partitions using SSIS and XMLA. Example: · We are going to drop our oldest partition from Measure Group called Fact InternetSales 1, which is in our Adventure Works cube. · The actual Cube partition name is called: o …
SSAS (SQL Server Analysis Services) – Getting all partition information from SSAS Database
I had a requirement where I wanted to find out and keep a constant record of exactly how my partitions were set up and created within SQL Server Analysis Services (SSAS). So below is a script that I found somewhere (If I find the source I will put it in hereJ) and how I inserted into a SQL Server Table…
SSIS – Creating new Partitions in SQL Server Analysis Services (SSAS) with XMLA and Analysis Services DDL
What I wanted to do is to create a process in SSIS that did not use code to create new SSAS Partitions using XMLA. NOTE: This is quite a long blog post, but it has all the details from start to finish to get this completed. Example: · We will be using the AdventureWorks2012DW data warehouse and Cube….
SSIS – Using SQL Server Analysis Services (SSAS) MDX query into Variable in Execute SQL Task
What I wanted to do was to get the output from my MDX query, and then use this in SSIS and take the output and put it into an SSIS Variable which could then be used later in your SSIS Package. Example: · We are going to get the Internet Sales Amounts for 2 days. o 01 June 2008…
Book Review – Expert Cube Development with SSAS Multidimensional Models
http://www.packtpub.com/expert-cube-development-with-ssas-multidimensional-models/book This book is a must read if you are looking for some useful and practical information in your SSAS Multidimensional models. But it must be noted that this is not an introduction into SSAS Multidimensional modelling, rather practical real world examples. I found that the book is a wealth of information that can be applied to your working environment….
SSAS (SQL Server Analysis Services) – Securing Measures in Measure Group for specific Roles
What I had to do today for the first time is to only show specific measures in the measure group for particular Roles (Users or AD Groups) · The reason that this was required is because we had sensitive information that was not for everyone’s eyes. NOTE: From my recent review and read through from the following book, http://www.packtpub.com/expert-cube-development-with-ssas-multidimensional-models/book…
Excel – User Input for Custom MDX query using VBA and Pivots
I had a requirement where I wanted to do the following for a particular Power User · Give them the ability to put in a Date as a parameter in an Excel Workbook. · Once I have this date, is to the have a custom MDX query, which will then go and get the required data for the date. o …
SSAS – Error – Value cannot be null. Parameter name: rootComponent
I got the following error as shown below, when I was adding rows into the XML file for the Data Source View. NOTE: The reason that I was using the XML file was because I had added a whole lot of partitions to my Data Source View, so I did not want to create all the relationships manually but rather…
SSAS – Using the SUM Function within a Measure Group to display a Distinct Count with SSAS (SQL Server Analysis Services)
Whilst completing my review on the Expert Cube Development with SSAS Multidimensional Models, I came across the Distinct Count Measure within SSAS and how this can affect query performance as well as processing performance. You can find the reference on page 114 And here is a link to the actual book that I am reviewing: http://www.packtpub.com/expert-cube-development-with-ssas-multidimensional-models/book What I wanted…
Win Free Ecopy of new book on SSAS
Readers would be pleased to know that I have teamed up with Packt Publishing to organize a Giveaway of the Expert Cube Development with SSAS Multidimensional Models And two lucky winners stand a chance to win ecopy of their new book. Keep reading to find out how you can be one of the Lucky Winners. Overview: …