A couple of weeks ago I did two webinars for the Pragmatic Works Free Training on The T’s. The first was a session on how to load a data warehouse with SSIS. The followup session was about getting started with SQL Server Analysis Services (SSAS). You can find the link to the recording here. The Q&A can be found below. Enjoy.
Q: Can I run SSAS Tabular and SSAS Multidimensional side by side on the same server?
A: Yes. The only limitation is that an instance of SSAS has to be one or the other. This means that when installing on the default instance (MSSQLSERVER) you have to choose if that instance will be tabular or multidimensional. If you want both on the same server a second instance will need to be installed. My general setup is the default instance as multidimensional along with the database engine and SSRS then a second named instance called TABULAR that is only the SSAS engine in tabular mode.
Q: How do you write data back to a cube?
A: Here is a really good blog post that walks through how to set it up step by step a writeback partition and even consume the feature in Excel. http://blogs.msdn.com/b/querysimon/archive/2011/03/03/writeback-to-analysis-services-cubes-from-excel-2010.aspx
Q: In tabular we can create calculated columns, how do we perform similar functionality in multidimensional?
A: The same functionality exists in Multidimensional but in a slightly different manner. This is accomplished through the use of named calculations in the data source view editor. Simply right click on the table name and select “New Named Calculation”. Here you can write a query to create a new column that will be persisted in the cube but not in the source database. There is a major difference in the way these columns are create though. In tabular these new columns are created using DAX and are executed against the Model in the tabular engine. In multidimensional however, the new column is created using the same query language as the source system (T-SQL for instance) and is actually added to the query that is executed against the source database when SSAS processes the data for the cube.
Q: How do we build IF…THEN…ELSE in multidimensional?
A: This is really a two part answer depending on the use case for this logic. First, if you are looking to do this type of logic on a column level to change values in the column like gender from “M” to “Male” that would be done in the DSV using a new named calculation. Second, if you are looking to do this in an actual calculation such as using a different calculation for the year level than the month level those would be performed in the cube editor. Once you open the cube in SQL Server Data Tools find the tab across the top called “Calculations”. Here you can enter MDX to create custom calculations including IF…THEN logic.
Q: What determines if I use tabular models or multidimensional cubes?
A: This is a long answer. I will take the easy way out and point you to a white paper on the subject. http://msdn.microsoft.com/en-us/library/hh994774.aspx
