Hot answers tagged

42

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


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

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


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


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

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

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

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

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

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

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

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


5

There are 2 ways you can go about this. You can either create a service account for the sql agent service and give that account permissions on the olap server, or you can create a sql agent proxy account. Personally I think the proxy account is the cleanest solution. To create a proxy account refer to this msdn article: Create a SQL Server Agent Proxy. ...


5

We really need more detail on your measures and dimensions to give you an exact answer on this, but since you are using SSAS a few general rules apply. If you add more detail I can expand my answer to be in line with your data model. Since you want to compare the number of transactions you should have a measure showing the number of transactions. As you ...


5

Assuming you have properly set the ValueColumn property of your CreatedOn attribute in your dimension (so that MemberValue comes back a date data type) then the following should work: select {[Measures].[Your Measure Here]} on 0, [DimAccounts].[AccountPK].[AccountPK].Members on 1 from [Your Cube] where { Filter( [DimAccounts].[CreatedOn].[CreatedOn]....


5

Your main problem is that you are storing a measure in your dimension. You will keep running into issues with multiselect and or aggregation unless you store the measure where it belongs, namely in the fact table. Either change your ETL to copy the max_weight in your fact table, or use a named query in your DMV joining your fact table to your dimension ...


5

You are confusing 2 things in your question. Since you state you have duplicate attribute key errors you are using the the age table as a dimension, but since you also want to find averages you are using the age table as a fact table. When using ages as a dimension you probably don't want every single age on your axis, so what is typically done is divide ...


5

TL/DR: Add more Memory, reduce the size of your Model(s), and/or move either the SQL Server Services or SQL Server Analysis Services to a different server (e.g. scale out) Longer Explanation: We went through this exercise a few months back with our Tabular SSAS production server, and actually reached out to Microsoft for "formal" recommendations as our ...


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