Tag Info

Hot answers tagged

27

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 ...


10

Think about a process or event you might want to analyze. Let's say you were building Lougle Analytics, and want to analyze visits† to your site. A fact table represents a process or event you want to analyze, in this case it is a list of site visits. What things might be useful to you in analyzing site visits? Information about the web browser ...


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

The book "The Data Warehouse Toolkit" by Kimball is a must for any fledging BI developer, IMO.


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, ...


5

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 ...


5

I tried to duplicate what you have using a multidimensional Contoso cube with a measure that is Min Inventory Day in Stock and didn't encounter your issue. I think it has to do with the current context when you aggregate [adhoc]. I don't have enough info to duplicate exactly. But this phrase caught my eye when looking at the specifications for MIN(): If ...


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

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

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 ...


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

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

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

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

You can try the following calculation: Openings:= 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 ...


4

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 ...


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 ...


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

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. Measures ...


3

Thanks to @ConcernedOfTunbridgeWells for pointing me in the right direction on this. The issue was with the status expression. Since it ALWAYS returns a 1, 0 or -1 it didn't account for NULLs. I added a check at the beginning of the expression: CASE WHEN IsEmpty(KpiValue("MyKPI")) THEN NULL ...which correctly leaves those fields empty.


3

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 ...


3

You can do this using MyODBC. Install the connector, then just set up a DSN and use it as normal.


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 <= ...



Only top voted, non community-wiki answers of a minimum length are eligible