A quick overview of SSAS for DBAs
So, you're a SQL Server DBA and you've just inherited some cubes out of the blue to manage. A quick crash course on SSAS administration seems to be in order.
From an administrative point of view, SSAS is a fairly straightforward, if resource hungry application. It's way simpler than a DBMS platform, although different in ...
Think about a process or event that you want to analyze.
Let's say you were building Lougle Analytics, and want to analyze visits (as opposed to single-page requests) to your site. Visiting a website is a process.
A fact table represents a process or event you want to analyze, in this case it is a list of site visits. You can have as many fact tables as ...
In the image below which is an example of a basic Star Schema. The Dimensions are the Dim_Tables.
These are generally the values that you want to analyse the data by. So you want to look at a particular product sales, in a particular country, over a particular date range.
In the fact_sales table you have just the one Measure which is Units_Sold.
HOW TO DO THIS WITH T-SQL:
As requested this is an alternative to my previous answer that showed how to do it per-user with Excel. This answer shows how to do the same thing shared/centrally using T-SQL instead. I do not know how to do Cubes, MDX or the SSAS stuff for this, so maybe Benoit or someone who does know that can post its equivalent...
1. Add ...
You could do this with an OLAP system - some of the benefits of SSAS for this type of application include:
SSAS can readily scale out - especially as this is a read-only application with no requirements for cube writeback.
Aggregations can be tuned to minimise the I/O allowing the cubes to be tuned for efficiency.
OLAP client software and third party ...
Processing a cube largely consists of 3 steps,
Getting the data
Step 2 and 3 are the least expensive (during processing) in my opinion so let's start with that.
Building indexes does little more than calculating bitmap indexes for your attribute relationships. So depending on how many of those you have designed ...
I agree the documentation on this is a bit terse, but the error message on the other hand is very clear. ImpersonateCurrentUser is not supported for models attached to a SSAS instance.
This is mentioned in the documentation of the ImpersonationMode Enumeration:
ImpersonateCurrentUser: Not supported for tabular model databases attached to an Analysis ...
I think it is feasible to force both services' hands by having a scheduled task or service that:
(a) before SQL Server's nightly processing, shuts down the SSAS service, and increases the memory allotted to SQL Server
(b) after SQL Server has done its processing, reduces max server memory and restarts SQL Server
(c) starts SSAS
This assumes that SSAS isn'...
This can in fact be done. There are probably a few ways to do it, and here is a fairly straightforward example. For this solution, you will use a combination of:
A SQL Agent job with a step for each instance that needs backed up (i.e. A step for the development server, the qa server, and for production).
One dynamic SSIS package that is called in each step ...
HOW TO DO THIS WITH EXCEL
Here's how I would do it in Excel...
1. Add SalaryRanges Excel Table
Insert a new worksheet, call it "Salary Ranges". In row one add the text headers "Min", "Max" and "Range" in that order (should be cells A1, A2, A3, respectively).
In cell B2 add the following formula:
In cell C2 add this ...
You can have more than one hierarchy on a dimension. Your time dimension could have a year-quarter-month-day hierarchy and a year-week-day hierarchy. The hierarchies can share the same 'day' attribute - set up attribute relationships for both of the hierarchies, and 'week' and 'month' can both be attributes of 'day'
That's not how it works. A .dsv (data source view) is generated by defining which tables/queries you want to use in your cube.
The flow is:
create one or more .ds data sources by defining how to connect to the source databases
create a dsv (data source view) by adding tables and named queries defining how to get data from your .ds
create dimensions and ...
While the cube builds, you can run Adam Machanic's sp_WhoIsActive diagnostic tool to see which queries are allocating space in TempDB. I recorded an sp_WhoIsActive tutorial video to show how it works. Include the @get_plans = 1 parameter when you call it, and you'll also get the execution plans. That way you can see exactly what's using TempDB and why.
Do SQL and SSAS ever need to run at the same time? If no, are you 100% sure?
I would look at setting the memory setting for SQL and SSAS to see if they can cooperate with one another.
We'll need to leave some memory for the OS, other processes, and multi-page allocations. Perhaps 4G?
Try something like this...
On the SQL side, set Max Server Memory to ...
SSAS is a very meaty topic. Almost none of what you know about the database engine can be applied to Analysis Services. If the only goal would be to provide a back-end for this report, then getting up to speed on Analysis Services and implementing the OLAP database would be a pretty substantial overhead compared to a more conventional approach of ...
As you correctly noted this is what happens when you try to display measures across a dimension to which they don't have a relation.
You basically have 2 options
Use an MDX solution
I would suggest you try the IgnoreUnRelatedDimensions first, as the measures would be aggregated better, and NonEmptyCrossJoins would be able to ...
You might get some of the answers regarding SSAS administration from this lengthy white paper SQL Server 2008 R2 Analysis Services Operations Guide. This is how the introduction begins:
In this guide you will find information on how to test and run
Microsoft SQL Server Analysis Services in SQL Server 2005, SQL Server
2008, and SQL Server 2008 R2 in a ...
Answers to your questions, in order:
First point: I'm not sure what you're getting at here. A measure can only be something that will display in a single cell. You can aggregate stuff up to create a measure. You can also have multiple fact tables (called measure groups) in a cube. If you slice by common attributes they measure groups will slice by those ...
In order for a specific role to have permissions over a cube data you have to specifically grant cube permissions to that role.
That can be done using the Cube tab in the role properties page: in SSAS
-> Databases -> Your db -> Roles -> your specific role -> Cubes tab.
You can assign specific permissions for:
Access - None, Read, or ReadWrite
You can try the following calculation:
CALCULATE( SUM( 'Requisition Counts'[NumberofOpeningsQT] )
, CALCULATETABLE( LASTNONBLANK( 'Requisition Counts'[SnapshotDateKEY] , 1 )
, ALL( 'Requisition Status' )
The important part is in setting the correct filter context for the ...
Analysis Services will set the data type of the dimension attribute to be the same as the source column. It is sorted exactly as you asked for it. Your days are declared as a character type. Therefore, they will be sorted according to character sorting (1, 10, 11) and not numeric sorting (1,2,3).
You didn't specify whether this was multi-dimensional or ...
That behaviour is dependent on your KeyColumns setting.
Given a cube with these 2 named queries in the datasource view
SELECT 1 AS id, 'India' AS country, 'Calcutta' AS city
SELECT 2 AS id, 'India' AS country, 'Bangalore' AS city
SELECT 1 AS city, 5 AS salesamount
SELECT 2 AS city, 5 AS salesamount
You have your terminology a bit wrong which makes it hard to tell what exactly you mean by your 2 options but you should use the OrderByAttribute (which is what I think you mean by option 1). The OrderByAttributeis meant to be used for exactly that, ordering the attributes by something else than their Key or Value.
If by option 2 you mean you create the ...
First of all, T-SQL and MDX are 2 completely different beasts. SQL is intended to query tabular relational data, while MDX is intended to query multidimensional data.
I'll start with addressing your question about common mistakes. In my opinion the most common mistake is to try and apply SQL knowledge to MDX. You really need to get into the ...
Just a follow up on how we decided to implement our DataWarehouse to support multiple Time Zones and be as efficient as possible:
We chose to create a table of time zones (id, name, etc...) as well as a "Time Zone bridge" table that looks like this:
Having the date and time separate will allow you to do aggregates by time much easily. for eg: if you want to run a query to find what time period of the day is most busy. This is much easily performed using a separate time dimension.
Also, you should just have one timekey. Decide on either GMT/ EST time - then use this in the fact table. If you need to run ...
I believe you seeing the symptoms of an issue with Windows 2003 requiring contiguous memory and this causes the running processes including Analysis Service to trim their Working Set memory. With large amounts of memory allocated this trim process can take a significant amount of time to complete, while this is running new allocations will be blocked, ...
Making SSAS hierarchies on slowly changing dimensions is a bit of a fiddle. You need to make surrogate keys for each historical version at each level of the hierarchy. Then the key has the actual business facing name, which the user selects or reports by.
As an example, imagine worker BloggsJ in Division1, which is in LineOfBusiness1. Now Division1 gets ...
I got it!
Basically the filter expression was correct, but I needed to Crossjoin the set what I wanted to filter against...
[Student].[Major].Properties("Key") = [Student].[Minor].Properties("Key")
I know I answered this quick but I've spent the best part of 10 hours on this ...
With the MDX language you can create custom members that will defines the ranges. The following expression defined a calculated member that represents all the salaries between 501 and 1000:
MEMBER [Salary].[between_500_and_1000] AS Aggregate(Filter([Salary].Members, [Salary].CurrentMember.MemberValue > 500 AND [Salary].CurrentMember.MemberValue <= ...