Hot answers tagged ssas
24
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 ...
10
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 ...
9
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 ...
8
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'
7
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.
7
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:
=IF(A2="","",IF(A3="","+",A3-1))
In cell C2 add this ...
7
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 ...
6
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 ...
5
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, ...
4
The most significant balancing act to consider is whether your need for real-time reporting outweighs the performance hit that both your OLAP and OLTP systems will take from using your OLTP data source directly as fact and dimension tables, and then bouncing ROLAP/HOLAP queries off of them. If the tables are all quite small, and the server isn't under heavy ...
4
I'd say less as an initial starting point but more as an indispensable resource, there are some great blogs out there:
SSIS Junkie/Jamie Thomson
SSIS Team Blog
4
BI Development is a big subject to learn and will take a lot of time understand all the concepts, I would suggest this book as place to start your journey of discovery: The Microsoft Data Warehouse Toolkit: With SQL Server 2008 R2 and the Microsoft Business Intelligence Toolset by Joy Mundy
Permalink: http://www.amazon.co.uk/dp/0470640383
4
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 ...
4
You're focusing on facts and forgetting about dimensions.
Years of service is a measurable attribute of a Driver, so, you should have a Driver dimension associated with the FactDeliveryDriver fact.
driverSid is a surrogate key for the Driver dimension, and not for the fact table. For the fact table it's a lookup column.
Also, deliveries are (probably) ...
4
I got it!
Basically the filter expression was correct, but I needed to Crossjoin the set what I wanted to filter against...
FILTER (
CROSSJOIN([Student].[Major].[Major], [Student].[Minor].[Minor]),
[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 ...
4
Please explain in more detail what you meany by "groupers"?
For instance, if you want to rank a member within a set, you need to tell SSAS what the set is that you are ranking within.
RANK( Tuple, Set, Measure to rank by)
So
RANK( Machines.Machines.CurrentMember, Machines.Machines.AllMembers, Measures.Efficiency)
Now, I think that what you are trying ...
4
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 ...
3
To answer your questions in order:
The cube doesn't store medians, modes (or even averages), but you can write queries that calculate them and embed them as calculated measures in the cube. The ability to embed this sort of computation is one of the main unique selling points of OLAP technology.
If you have a dimension that can identify individual rows ...
3
You can do this using MyODBC. Install the connector, then just set up a DSN and use it as normal.
3
I eventually managed to track this down to low memory. I was under the mistaken impression that 32-bit Analysis Services could use AWE memory, but alas it does not. I changed the processing strategy from full processing, to incremental processing of only the appropriate partitions. That seems to have reduced the memory requirements, and resolved the issue ...
3
I went through a pretty thorough 2008R2 training a year ago. It wasn't radically different from 2005, but it was different enough that you're going to face quite a bit of frustration when you can't do things in 2005, or the things you want to do are done differently. SSAS has been changing quite a lot over the last several releases. This is the list of ...
3
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 ...
3
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 <= ...
3
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
...
3
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 ...
3
MDX Drillthrough allows you to specify a limit for the row set returned. As far as I am aware it doesn't have a 'show how many rows this would produce' facility. You could fake it by having a count measure for each of your fact tables and selecting this for the slice in question before you execute the drillthrough.
I'm not sure whether Excel 2010 has ...
2
Connect to your Analysis Services server in SQL Management Studio. Right-click the server, then click Properties in the menu. In the Properties dialog, check the "Show Advanced (All) Properties" check box at the bottom of the list. Then, in the grid above that check box, set the location you want for SSAS TempDB in the "TempDir" setting.
You will have to ...
2
You need to alter the location using ALTER DATABASE tempdb MODIFY FILE location and restart SQL Server. SQL Server will automatically create the tempdb in the new location but you may have to go to the old location to delete the (old) files.
See Moving the tempdb database in http://msdn.microsoft.com/en-us/library/ms345408.aspx
2
Thanks to @JackPDouglas for the guidance, I think I may have an answer to my own question. It's a hack but I think it will work:
The user is interested only in some types of payments and some types of products. I will attach subquery only for those payment types in the detail view:
select
case when exists (
select top 1 1
from ...
2
You need to start off with a cross join of detail and payment, because you say "products in any transactions that includes a certain type of payment are considered to be paid with that payment type", so you are effectively asking for parts of a transaction to be counted multiple times if there are multiple payment types used.
Only top voted, non community-wiki answers of a minimum length are eligible
