Hot answers tagged

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


10

I need to query Analysis Service with OPENQUERY, handle the query of length greater than 8000, save the results to a temporary table or select them directly. sql-serverssasmdxEdit tags Instead of OPENQUERY use INSERT ... EXEC ... AT LinkedServer, like this: create table #t(id int, a int) insert into #t exec ('select 1 id, 2 a') at LinkedServer select * ...


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

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


7

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


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

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


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

from the comments by Denis Rubashkin Try to use "Run as administrator" when running SSMS, it solved the same problem for me and that worked for me too. to run ssms as and administrator press the shift and right click on the ssms icon and that will give the options below:


4

You can do it using powershell. $serverName = "servername\instanceName" $outputFolder = "D:\data\" ## load the AMO and XML assemblies into the current runspace [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.AnalysisServices") > $null [System.Reflection.Assembly]::LoadWithPartialName("System.Xml") > $null $dateStamp = (get-Date)....


4

Try WITH Member [Age Band] Instead of WITH set[Age Band] Since you are doing a calculation based on the [Measures].[Age] Try the following and see if you can work with WITH MEMBER with member [Age Band] AS IIF([Measures].[Age]<26,"0-26",IIF([Measures].[Age]<51,"26-51","Over 50")) SELECT {[Age Band] } DIMENSION PROPERTIES ...


4

Checking whether the dates are contiguous You don't say which DBMS you're using here, but you're using SSAS so I'm guessing SQL Server. If you're on a recent enough version, using LAG and LEAD in window functions can be really handy for this kind of task. You can order the rows by the start date or by an incremental ID if you have one, and then use these to ...


4

Depending on how much RAM is on your server, below is a good starting point (given you have SSAS ONLY running on the server) : click to enlarge: Refer to :SSAS Memory Configurations for Common Architectures and Analysis Services Memory Limits SSAS uses memory limit settings to determine how it allocates and manages its internal memory. Memory\...


4

Without sample data or structure it would be hard to provide the exact code, but here goes. You need to look into the PARALLELPERIOD function. That function takes a Level from a hierarchy and navigates back a number of steps in that level. Also, I'm using the assumption here that you want to use the TAIL() function to get the last month in your dimension ...


4

tl;dr; SSIS moves data. SSAS creates cubes. SSRS displays data. Given the following problem statements The client cannot directly query the database. The client can only query the cube. Therefore, you're going to need to modify the existing SSAS cube solution to expose the missing data for them to consume. Creating a new table in the database isn't going ...


4

If you just transfer the data to another server using SSIS it won't magically appear in the SSAS database, so you would need to change the SSAS solution. If your question is about providing access to the production database you could use SSIS to transfer data onto another server where the user does have access, but it would need to be access to the ...


4

The problem in this case was that I was trying to replace an existing column also named 'Date Offset' - this was not a calculated column, but taken from the source table. Resolution was to do the deployment in 2 phases, first remove the old column, then deploy again to add the new calculated column.


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