Questions tagged [ssas]
SQL Server Analysis Services, an OLAP server bundled with SQL Server by Microsoft since version 7.0
350
questions
0
votes
0
answers
87
views
Performance issue in SQL Server Analysis Services Multidimensional Data Model
We have a star multidimensional model in SSAS in SQL Server 2019 with 4 fact tables and about 600 million records on each fact and many dimension tables with about 1.5 million records and there is no ...
- 159
0
votes
1
answer
66
views
Installing SSIS, SSAS and SSRS on a separate Server
Please is there any benefit from installing SSIS, SSAS and SSRS on a separate server from the SQL Engine instance?
Apart from additional license implications, are there any other considerations for ...
- 303
1
vote
0
answers
15
views
DrillDownLevel causing rows to be duplicates
I have defined a cube with a hieararchy of Dates such: Year is a top level then there's Month level.
I have deployed the cube using SSAS VS 2022 and SQL Server 2019.
But when I query it using the ...
- 111
0
votes
1
answer
76
views
SSAS Backup to Remote Location
I followed the steps in the best answer on this question - How can I dynamically back up all SSAS databases on a given instance? - and can successfully create backups of our BI Cubes to a local drive ...
- 101
0
votes
0
answers
33
views
OLAP Service stopping out of the blue and Not starting immediately when rebooted
So I have analysis server installed and the services just get down for some reason also when the server is rebooted. I installed a script binding it to the SSAS service to automatically start when ...
0
votes
0
answers
25
views
how can I create a Query log can be enabled for Analysis Services tabular mode instances?
I know that "Query log can be enabled for Analysis Services multidimensional mode instances only. Query log cannot be enabled for tabular mode instances." But is there a way I can do query ...
1
vote
1
answer
490
views
How to release SSAS memory without forcing restart
We have a production server with two SSAS instances, 1 for user querying and 1 with empty templates where we do new releases and (full) processing of the cubes (and then backup and restore processed ...
- 11
0
votes
0
answers
43
views
MDX Calculation for fact value through dimension attribute
I've got a dimension [D] in my model containing an id [d_id] and an attribute [power].
And I'm having a Meassurement containing an value [prod] which is connected to the dimension over it's id.
Now I ...
- 123
0
votes
0
answers
33
views
Tabular Cube SSAS Enterprise - Ignoring "LowMemoryLimit"(And All Others)
I have a really big SSAS Tabular cube server, it's has about 1TB of available memory(x1e.8xlarge). I have allot of cubes on it, it's great, I want to add more, I've added a new one and unfortunately I'...
- 205
0
votes
0
answers
44
views
Does Power BI Premium Model have ALL Analysis Services Capabilities?
It is apparent that there are many Power BI Premium Model capabilities which are not in Analysis Services (such as AI). For the purpose of this question, I am mainly concerned with some less modern, ...
- 351
0
votes
1
answer
597
views
SSAS memory limits
SSAS service is configured using standard settings with a low memory limit 65. Accordingly to MS it means 65% of the available RAM and hardware host has 256 GB RAM, but SSAS always stops at ~65GB and ...
- 171
0
votes
1
answer
1k
views
No connection to the analysis service
I can not connect to the analysis service at all. I installed three instance examples of SQL Server on my laptop.
SQLSERVER2019 Developer Edition
SQLSERVER2017 Developer Edition
SQLSERVER2008 R2 ...
0
votes
0
answers
56
views
Product Version for Analysis Services and Report Server using T SQL
How can I find the product version for both analysis services and report server using Transact-SQL?
I'm trying to put together a query that will get me the details of each SQL instance with their ...
- 131
2
votes
1
answer
883
views
How to find last processed time of dimension in SSAS
I have a list of around 100+ dimensions. Is there any way to check the last processing time of each dimension using DMV's in SSAS?.
Thanks in advance
- 71
1
vote
1
answer
273
views
How to install SSAS in SQL server 2019
I installed SQL Server 2019 and Analysis Services.
Now I need to install SSDT to create Analysis Services projects.
I did some research and saw that to create the SSAS projects it is necessary to ...
- 11
0
votes
0
answers
388
views
Cube won't deploy
I was able to deploy a large cube (with 2 dimensions and 1 measure) in Analysis services server but only after setting configuration error options for both dimensions and the measure (through the ...
5
votes
2
answers
1k
views
8000 character limit in OPENQUERY against Analysis Server
I have a query like
SELECT column1, column2 FROM OPENQUERY(AnalysisServerName, 'MDX QUERY ...').
It is inside a stored procedure. A MDX query is dynamically built in it and the length of the query ...
- 51
1
vote
0
answers
155
views
SSAS 2019: Complex stored proc querying OLAP goes slower after upgrade
I've performed an in place upgrade of Windows 2012 to 2019 and SQL Server from 2016 to 2019 on my Dev server.
Since the upgrade we have found one process related to financial forecasting has slowed ...
- 3,203
0
votes
2
answers
1k
views
What version of MSOLAP provider am i using?
We've just upgraded a Server from SQL Server 2016 to 2019.
We have linked servers connecting to an OLAP cube.
When I look under providers for MSOLAP the label of the dialog box shows:
Microsoft OLE ...
- 3,203
1
vote
2
answers
429
views
Server side tracing for SQL Server Analysis Services
We are looking to continuously run server side trace on Analysis Services and record its output in a table in database in the Database Services.
There are tons of articles showing server side trace ...
- 159
1
vote
1
answer
242
views
how to find the error description for the error codes found in the extended events file XEL generated by SSAS Tabular query end event
how can I translate the error codes logged into XEL files to the corresponding description?
I'm importing the XEL files to a SQL Table using the sys.fn_xe_file_target_read_file function, but the ...
- 159
0
votes
1
answer
52
views
Data Type to use for Exchange Rate in SSAS Multidimensional
So we have a cube setup that does a many-to-many currency conversion. Until this point, the Exchange Rate measure group has used a Currency data type for the exchange rate, but I didn't realize that ...
- 231
0
votes
1
answer
2k
views
SSAS error deploying project: Ensure that 'SQL Browser' service is running
After successfully building my project, I got this error when deploying it:
The project could not be deployed to the '\SQLEXPRESS' server because of the following connectivity problems :
A connection ...
- 3
1
vote
0
answers
10
views
Force use of System.Date in SSAS
I would like to know idf it is possible to force System.Date use in an SSAS data source view.
For now, I have a view with a field of type Date and in the data source view it is converted to a System....
- 141
1
vote
0
answers
76
views
SSAS : are dimensions with only Id and Name attributes useless?
I wonder if it is worth creating a dimension with only one column (e.g. Name) in addition to its Id Column. This dimension would be referenced by a fact containing a reference to the Id attribute of ...
- 141
2
votes
0
answers
460
views
startup ask failed for service MSSQLServerOLAPService
I can't install SSAS (SQL Server analysis services) in multidimensional mode : I get an error at the end of the installation : startup ask failed for service MSSQLServerOLAPService. I was able at the ...
- 141
0
votes
1
answer
75
views
SSAS- Dimension Attribute needs to be sorted
In one of my SSAS- cube one attribute(inset_date) which is Date datatype and format is yyyy-mm-dd, when I checked in excel pivots it is not coming in order.
I have changed the order by property from ...
1
vote
2
answers
1k
views
How can I script administrator permissions on a SSAS server?
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 ...
- 15.8k
0
votes
0
answers
117
views
I am not seeing Management Folder in SSAS but I do see it in SSMS. What am I doing wrong?
So I am trying to turn on extended events for my SSAS multidimensional cube. However, when I am logged into SSAS (with full admin priv) I only see Database & Assembly folders as seen below.
I am ...
- 3
1
vote
1
answer
137
views
Do I still need OLAP cubes after a SQL Server Upgrade
After our DBA got sacked for poor performance, I am now our accidental DBA. I have been pushing to upgrade our SQL Server instance from 2012 to 2017 so I can use the clustered column store indices in ...
- 113
0
votes
1
answer
133
views
Design Sql Server physical configuration from existing VM
We have 4 virtual machines that we used to test our BI platform (PoC), the final dimensions are:
SSIS (16 vcpu), SSAS (8 vCPU), SSRS (4 VCPU) et Data Engine (24 vCPU)
What are the recommendations ...
- 1
0
votes
2
answers
782
views
how to find backup history of SSAS database
Shockingly on Internet, I am unable to find backup history of SSAS database.
Is there no way to do it?
Also should we take multiple backups of SSAS database? I am not much aware of SSAS database.
...
0
votes
1
answer
104
views
Processing Dimension completed but error occurs
I have problem while processing cube in SSAS, everytime I have an error:
I tried to process every dimension separately and it looks like something bad was going on after processing 'Date' dimension. ...
-3
votes
1
answer
410
views
SSAS database backup [closed]
I tried to backup SSAS databases (2008 R2) by using query in SSMS, but it is overwriting previous day's backup. How to take backup without overwriting previous backup. I need to maintain at least 7 ...
- 161
0
votes
1
answer
423
views
Restore from .db folder for Analysis Services
There is an objective to recover cube from long time ago retired MSAS instance. Issue I'm currently standing with is that actual .abf backups are now in a cold storage and recovering them would take a ...
- 101
2
votes
1
answer
2k
views
Does anyone deployed a succesful SSAS on docker?
I'm trying to create a docker image that contains MS SQL server, analysis services, and restore a db (bak) and a cube (abf). The Sql server part is the easy stuff, and I imagine that the restore will ...
- 37
2
votes
2
answers
2k
views
Authentication failed when deploy an Analysis Services project
I am getting the following error on Analysis Services project on Visual Studio 2012 and SQL Server 2014:
The project could not be deployed to the 'MyServerName\InstanceName'
server because of the ...
- 305
1
vote
1
answer
586
views
ProcessUpdate of the dimension triggers processing of all partitions of all measure groups in the cube
I have Account and Customer dimensions in the cube that are connected to the same measure groups (there are about 15 - 20 measure groups in the cube).
When I run XMLA command to process update these ...
- 111
2
votes
2
answers
2k
views
StorageEngineUsed set to TabularMetadata. For databases in this mode, you must use Tabular APIs to administer the database
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 ...
- 15.8k
0
votes
1
answer
264
views
How to have MS Analysis server setup of both modes: multi dimension and tabular
Ref: Is there a way to install both tabular and multidimensional modes?
The answer was .."You answered your own question. You simply install another instance of SSAS."..
My MS SQL 2014 has a ...
- 1
1
vote
1
answer
494
views
remove Analysis service from a SQL 2014 cluster
Is it possible to uninstall Analysis service from a SQL 2014 cluster. I’ve seen that it cannot be done in 2008/12. I’m hoping that this maybe fixed now
1
vote
0
answers
386
views
Error while trying to process dimension in SSAS
I am getting the below error when I try to process my dimension in Analysis services from SQL Server Management Studio.
Errors in the OLAP storage engine: The attribute key cannot be found when ...
- 131
2
votes
1
answer
3k
views
Use Powershell to query SSAS connection string
I want to retrieve the existing connection string from SSAS for each OLAP Cube or Database. The data will be used in a control/audit report.
I've found that Powershell allows me to drill into SSAS ...
- 3,203
0
votes
0
answers
120
views
Running Sql Analysis server on different server
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 ...
- 37
2
votes
1
answer
653
views
SSAS Tabular model Date Dimension with Time possible?
I am in the process of building my first SSAS Tabular model and thought everything was going well, until trying to create a measure by DateTime.
In my warehouse, I have a DIm_Time dimension, which ...
- 540
2
votes
1
answer
1k
views
SSAS Tabular Model or SQL Relational Database for new Datawarehouse? Is SSAS still a good to learn technology?
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 ...
- 540
0
votes
1
answer
2k
views
Errors in the OLAP storage engine: A duplicate attribute key has been found when processing
When I try to process my cube and specifically the Employee_DIM, I get the following error :
Errors in the OLAP storage engine: A duplicate attribute key has been
found when processing: Table: '...
- 131
0
votes
1
answer
25
views
Can anyone please tell me if these are correct statements about measure group partitioning in ssas?
A partition is based either on a table or view in a data source, or on a table or named query in a data source view. The location where partition data is stored is defined by the data source binding. ...
- 33
2
votes
1
answer
620
views
How to log SSAS $system.discover_sessions to a SQL Server Database
We are running SQL Server 2014 and SSAS on the same box. Every now and then our SSAS service appears to freeze. When it does I check the msmdsrv.log file and it tells me that spid x is blocking ...
- 2,109
0
votes
2
answers
1k
views
SSAS Tabular model table load from query with #temp table fails
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 ...
- 23