Hot answers tagged

41

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


13

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


12

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


11

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

Processing a cube largely consists of 3 steps, Getting the data building indexes calculating aggregations 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 ...


10

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


9

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


9

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


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'


8

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


8

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


7

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


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


7

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


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


6

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


6

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


6

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


6

That behaviour is dependent on your KeyColumns setting. Given a cube with these 2 named queries in the datasource view Cities: SELECT 1 AS id, 'India' AS country, 'Calcutta' AS city UNION SELECT 2 AS id, 'India' AS country, 'Bangalore' AS city Sales: SELECT 1 AS city, 5 AS salesamount UNION SELECT 2 AS city, 5 AS salesamount ...


6

The easiest way is : using SSMS -- connect to instance --> reports -> standard reports --> General There are other ways as well - How to find Analysis Services Server Version ?.


6

You could use powershell to connect to the database and list a lot of properties. Use this script to find out what properties you can get from a database: $loadInfo = [Reflection.Assembly]::LoadWithPartialName("Microsoft.AnalysisServices") $server = New-Object Microsoft.AnalysisServices.Server $server.connect("localhost") $Server.Databases | Get-Member ...


6

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


6

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


5

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


5

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


5

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


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


5

I found this article here. There it states that: To resolve that problem you have 3 options: Run SQL queries from data server (you need to be remotely connected to the database server) Enable use of Kerberos on the database server Set proxy account for linked server, so that MDX queries are executed in its context instead of in context of the user that is ...


5

I think you are confusing things. First of all, processing the cube and processing the dimensions is sort of separate. I think you mean "processing the entire database". If you process only dimensions, and not the cube, the measures will never be updated. You could Process Update the dimensions which wouldn't unprocess the cube but would drop the indexes ...


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