Named instance MS SSAS don't accept connections after NTLM incoming/outgoing traffic deny. There is SPN for this server like: FQDN SPN: Setspn -s MSOLAPSvc.3/AW-SRV01.AdventureWorks.com:AW-FINANCE AdventureWorks\SSAS-Service NetBIOS SPN: Setspn -s MSOLAPSvc.3/AW-SRV01:AW-FINANCE AdventureWorks\SSAS-Service Also default instance on the same server works correct without any problems. SPN was added same way: Setspn -s MSOLAPSvc.3/AW-SRV01.AdventureWorks.com AdventureWorks\SSAS-Service

I'm trying to deploy a tabular model to a server using the "Analysis Services Deployment Wizard". When attempting to deploy,I get the below error. > The JSON DDL request failed with the following error: Failed to execute XMLA. Error returned: 'The column 'Date Offset' in table 'Date' has invalid bindings specified. The column in question uses the below calculation, which was found here INT([Date] - TODAY()) What should I look for in order to resolve this error?

I have an intermittent fault in my SSIS ETL process when I try and process my OLAP cube. There are no error messages in the SQL log, and there's nothing else significant running at the time. The only error message I can find in the event log is: > An error was encountered in the transport layer. SSIS and SSAS are SQL are all running locally on the same server, so I don't believe it's network related at all. The same package has been deployed and has run successfully for weeks. If we manually connect to the cube it processes without any problem. Windows Server 2012 R2 6.3 (Build 9600: ) on VMWare 64gb of RAM. Microsoft SQL Server 2016 Enterprise (SP1-CU1) - 13.0.4411.0 SQL Max mem = 48gb My gut feel is that this is more of an SSIS issue than a SSAS issue. Has anyone else encountered this? Can anyone reccomend a solution or workaround? Is it possible to get additional debugging information?

I am an administrator in SSAS . I have many data warehouse servers. In some servers I have SQL Server and SSAS on the same machine . I successfully backup the SSAS databases and even check if the backups are healthy Recently, however, one of our servers which is managed by a third party company based in Canada, needed to be replaced, and while migrating all the ssas databases from the old server to the new one this is the old server: this is the new server: I am getting this error message while processing the database in the new server: > This command cannot be executed on database 'DWCA' because it has been > defined with StorageEngineUsed set to TabularMetadata. For databases > in this mode, you must use Tabular APIs to administer the database.

I am an administrator for a bunch of SSAS Instances. Sometimes I need to install or migrate SSAS instances to different machines or even different AD domains (we have a couple of different AD domains in a trusted relationship ). Same as with sql server, I need to be able to export and import logins and permissions between instances. Also, as part of maintenance plan I like to save logins and permissions as well as backup ssas databases . I would to script the administration logins via script either T-SQL or Powershell so that I can automate it. at the moment, because I was in a rush I did it in this way: 1. Connect to the original server as administrator and script all the permissions into a mdx file. 2. Copy this script and run it on the target server. How can I script administrator permissions on a SSAS server?

We have several situations where complex queries are being used for creating/populating tables in our SSAS tabular model. The actual use cases cannot be presented here, due to both proprietary information and pure length of queries, but the following test case illustrates the situation: SELECT s.Servername, i.instanceID, i.InstanceName, i.ClientVersion, i.MajorVersion INTO #InstanceList FROM ServerLookup s INNER JOIN InstanceLookup i ON i.ServerID = s.ServerID SELECT i.Servername, i.InstanceName, i.ClientVersion, i.MajorVersion, im.EntryDescription, im.MonitorID INTO #InstanceMonitoring FROM #InstanceList i INNER JOIN InstanceMonitor im ON im.InstanceID = i.InstanceID SELECT i.ServerName, i.instanceName, m.MonitorName, m.MonitorDescription FROM #InstanceMonitoring i INNER JOIN MonitorLookup m on m.MonitorID = i.MonitorID The query runs fin in SSMS, but when we attempt to create a table in our tabular model, it throws the following error message: > Failed to save modifications to the server. Error returned: 'OLE DB or ODBC error. '. There is no further information on the error. I haven't been able to locate any documentation stating that temp tables can't be used in creating a tabular model table, and I'm reluctant to tell my developer community it can't be done without some information to back it up.

i have server running a database on it, and i have another server which i want to run Analysis server on it, so people can connect the analysis server as they wish to create their own reports for some purposes, but the problem is i could not connect Analysis services to database server which Analysis server can cube the data, according to my research database server and analysis server could be separated, but i could not find any information about how to connect analysis server to database server, the features i install on analysis server are Detailed results: Feature: Analysis Services Status: Passed Feature: SQL Browser Status: Passed Feature: SQL Client Connectivity SDK Status: Passed Feature: SQL Client Connectivity Status: Passed How can i connect Analysis Server to database server which is a different server. any help is appreciated

I am in the process of creating a data warehouse using SQL Server for my company. I have created a POC with a simple SQL Relational database with a few(about 10) stored Procs for the ETL process. Now that I have business buy in to step it up to a production state, where I will be tripling the data, it's the right time to ensure the technology I use is best suited for our needs and is aligned with general best practices. We will be processing about 500k records per day, with a maximum guess at about a million a day. The POC then aggregated all this data down into 5-minute chunks per client per day for the last 4 years. Total being a bit over 500k time slots, for each fact (POC had 4 fact tables) for each customer (Lets plan for 25). So for the POC, we are looking at approximately 50 million rows across all the facts by client and time. That leaves final views of the data at minor amounts, given that the last 6 months satisfies 85% of all questions, that means we should be sitting with result sets of approx 250k rows for 85% of the time if not less. Given that I expect to triple data, let's call it a maximum of 1 Million rows to satisfy 85% of queries, split across about 8 to 10 fact tables with about 4 or 5 dimension tables. With that all said and done, as mentioned, I am in a very fortunate position where I can now choose the technology that is used for the backend of the warehouse. I have never really had the opportunity to really get entangled in SSAS and feel like its a technology I am lacking. Is SSAS still a popular choice for Data Warehouses or has its popularity declined over the past few years? Is it still used as much as it was 5 years ago? SHORT VERSION: Is SSAS still something worth learning/investing time and effort into? We are currently on SQL 2016 and this will be built on SQL Server 2017 Thank you for all your input.

I had a third party company coming over and building ssas databases to produce reports. They did, but now the DBA wants to have a look but I haven't got permissions, to even see those databases, as you can see on the picture below. Can I grant myself administrator permissions? I am system administrator. there is a AD group called SQLDBA and When right-cliking on the server-properties-security-Add- then adding my AD group SQLDBA it says I don't have the permissions to alter the server object as you can see on the picture below. I had previously added that sqldba AD group as an administrator to that box. TITLE: Microsoft SQL Server Management Studio ------------------------------ Either the 'mycompany\MMiorelli' user does not have permission to alter the 'myserver\instance' object, or the object does not exist. (Microsoft.AnalysisServices.AppLocal.Core) ------------------------------ BUTTONS: OK

I would prefer to have a script to achieve this, but as I am not so familiar with SSAS I am using the wizard to create an extended events session in SSAS. The way I am doing it is right clicking on the following place and getting a new session created: wonderful, but on the second screen, I pick this default tracking to start with, which is fine, but then I get this nasty error message that I am not aware as how to fix: There are no valid data storage targets for this session. I move on, to the events tab and check which events are included Just to get stuck in the next tab, Data Storage: which does not allow me to proceed. How can I get over this There are no valid data storage targets for this session. or even more specifically, how can I set up an extended events session to track what is going on inside my ssas? I would prefer a script, but any ways to see what processes and\or queries are running inside my ssas databases is fine.

I have one server for data warehousing, and it has 2 instances of SSAS (SQL SERVER Analysis Services). The data warehouses are not that big, less that 200GB, and I backup the whole thing on a daily basis. Now they have asked me to patch\upgrade it: > Thank you yes, this indicates it is a 2016 SP1. The reason I ask is > that, as I indicated on Friday, when we tested the creation of > measures (e.g. year-to-date net applications) in Power BI from a live > connection to your Analysis Server, this caused unexpected errors. Our > research shows that this is very probably caused by your Analysis > Server version, and the solution is to install a Cumulative Update > (CU) 3 for SQL Server 2016 SP1 , or a more recent CU (as you can see in > the list on the left of the page, these go up to CU 11). As far as we > know these are free to download, but you may need to ask your > consultants for help. I wish I had different servers for LIVE and TEST environments. Where can I find information about the costs of licensing involved for a small server just to run a SSAS instance? I am talking about a simple, single, machine, with windows server 2012 R2 and the latest ssas. Also, patching for SSAS is anyhow different from sql server? I find licensing costs a tricky topic sometimes, with not-so-clear or contradictory information around.

I want to record measures from a Tabular model, as KPIs, and store the values in a table in SQL Server. I have created a linked server from my SQL Server instance to my SSAS instance. I have written a stored procedure to execute the DAX code via OPENQUERY, with the intention of storing these in a temp table, before loading them in to the KPI table. I am using a temp table because I am querying multiple tabular models. My problem occurs when I try to update my temp table with values from my OPENQUERY output. My OPENQUERY output is currently within a CTE, and I was hoping to do a simple join to the temp table, but because the output from the DAX query returns each column name within [ ], when I try to join on one of the OPENQUERY columns I receive the error "Invalid column name...". E.g. UPDATE temp SET temp.[Current Contract Count] = cte.[Contract Count] FROM #ServiceZoneKPIs AS temp INNER JOIN tabular_cte AS cte ON cte.[Copy of Service Zone Code] = temp.[ServiceZoneAlternateKey] The error occurs because 'Copy of Service zone' does not exist in the OPENQEURY output; the output column name is [Copy of Service Zone]. I may well be missing a simple trick here? How can I join an OPENQUERY output, returning tabular model data, to my T-SQL temp table?

Should I expect any problems installing a SQL Server Analysis Services 2016 tabular instance next to a SQL Server 2014 database engine? (e.g. due to an update of some shared components) I'm mostly worried about any issues related to the running database engine. Thanks.

I am developing a number of tabular models, which currently have a limited number of users assigned to roles within each. At the moment, with the exception of 1 or 2 cases, the majority of the users are "full read" users in all the reports. I am in the process of implementing row-level security in to a number of these roles and I am getting to the point now where, before these models get deployed in to live I think I should be developing a security model to be able to easily add/remove members to/from these roles. My question is, how can I deploy a model that allows for dynamically adding and removing users, without having to deploy the solution each time. In my mind my options are as follows: 1. Do this manually... not dynamic! 2. Create user groups in AD and add the user groups to the roles. This will be ok for "full read" users (e.g. Directors), or "manager" roles (e.g. sales manager for sales reports), but would not work for users that will be using row-level security (e.g. salesperson). 3. Create a "global security" table to add to the model, which contains the 'variable' for read access permissions for "full read" access (e.g. Directors and Managers). I will also have another security table that will hold the data for row-level security for other users (e.g. salesperson filters). Then each role in the model will essentially use row-level security based on these security tables. 4. Use TMSL to process each deployment in the SSAS instance and "createOrReplace" each role deployment where this user exists/needs to be added... unfortunately I don't know any TMSL so I don't know how hard this is to learn? I want to maintain a clean environment, so when somebody looks at the model in future they don't see users who left years back. Ultimately, when somebody leaves their AD account will be deactivated so this is less of an issue and more of an OCD. However, new members will need to be added. I'm leaning towards option 3, but are the above options my only choices? Has anybody deployed a different model? Am I missing something obvious!?!?!? I hope not. Thanks in advance for any help/tips/advice. EDIT: I have also just thought of using PowerShell to add/remove users. Based on what David said, I could get one of my guys to maintain this instead.

When you set SQL Server SSAS to MOLAP, and default processing, it detect changes in the relational source and only loads those changes into the SSAS MOLAP storage at processing time. But how does it detect those changes in the first place? Preferably answers will refer to some Microsoft documentation.

I need to make my tabular model case sensitive. Currently my tabular model is case insensitive, how do I change my existing cube to be case sensitive?

I have an indexed datetime column - lets call it "CreatedDateUTC" in table "Entity" in MSSQL db. Column contains UTC datetime. User connecting to db has known timezone offset. I have another table - DateDim with primary key column Date. This is to support date dimension for SSAS/Tabular model/Direct query scenario. So on model level "Entity" is defined as SQL query SELECT CAST(fn_AdjustCurrentTimezoneOffset(CreatedDateUTC) as Date) as CreatedDate FROM dbo.Entity The SQL query featuring DateDim from report then looks like SELECT * FROM [Entity] e JOIN [DateDim] d on CAST(fn_AdjustCurrentTimezoneOffset(e.CreatedDateUTC) as Date) = d.Date which is unbearably slow when [Entity] table grows large. Even if i resign on adjusting timezone (which I don't want to), it doesn't get much better - i.e. this query is slow as well: SELECT * FROM [Entity] e JOIN [DateDim] d on CAST(e.CreatedDateUTC as Date) = d.Date My feeling is the issue here is with CreatedDateUTC datetime - it needs to be converted to indexed CreatedDateDayUTC date upfront (not a problem since I query special reporting db). My problem is with the timezone - since users have different timezone offset I cannot simply precalculate this. On SQL level I would be able to get around this by this ugly hack - precalculate three dates (Date1=DATEADD(day,-1,CreatedDateDayUTC)/Date2=CreatedDateDayUTC/Date3=DATEADD(day,1,CreatedDateDayUTC)) on Entity table and adjusting join to something like: SELECT * FROM [Entity] e JOIN [DateDim] d on CAST(e.CreatedDateUTC as Date) = d.Date and (e.Date1 = d.Date or e.Date2 = d.Date and e.Date3 = d.Date) However this can't be translated to query defining "Entity" on model level (there has to be just one column which can create relationship do DateDim Date column). Any idea how to get around this ?

I'm building an SSAS OLAP cube on Meteorological data. Some of this data can be totaled (eg rainfall) and some can't (like wind direction). I want totals, but only on data sources where it makes sense. (Where the interpolation method = 3.) I've tried adding SCOPE with this: SCOPE ([Measures].[Total]); SUM([Data Source].[Interpolation Method].&,[Measures].[Total]); END SCOPE; And get warnings: > Invalid query syntax. A valid MDX or DAX query was expected and: > One or more errors were encountered in the MDX script What am i missing?