SQL Server Analysis Services (SSAS) is an online analytical processing (OLAP) and data mining tool in Microsoft SQL Server used as a tool by organizations to analyze and make sense of information possibly spread out across multiple databases, or in disparate tables or files. Analysis Services includes a group of OLAP and data mining capabilities and comes in two flavors - Multidimensional and Tabular.

Share tech news, updates, or what's on your mind.

Sign up to Post

I want Excel to prompt me each and every time for credentials each time i try to connect to the cubes. I don't want them saved ever.

Where does that setting live?
Get expert help—faster!
LVL 12
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

the two servers involved are sql 2014 and sql 2016
Our billing system is an OLTP database and it is very hard to report off of.   I have been reading on OLAP and ssas.  Is there a good manual or starting place for a rookie to try to overcome the reporting issues.
any ideas on should i start with trying to use ssas or creating an olap database from the online transactional database.
Hi, i'm need to know it's possible to create Live Data warehouse.
to be transfer data when adding new records or any changes immediately

I'm getting the following error when I try and process my SQL data cube:

Errors in the OLAP storage engine: A duplicate attribute key has been found when processing: Table: 'dbo_DIM_GEO', Column: 'CITY', Value: 'COLUMBUS'. The attribute is 'CITY'.

How do I correct the issue?
Power BI desktop and url connection to SSAS  cubes using the http msmdpump.dll. How to?

We have a url setup that gives a user access to our SSAS Cubes in Excel. That works fine no problem. (

But now we have a new user that needs to do the same thing in Power BI.

When I enter the URL in the prompt for server it gives me an "unsupported data format" error.

How do go about doing this for Power BI?

I created a SQL data-cube in SSAS called Test. I logged into SSMS, went to the Test database and added a role called Analyst. I added myself and another guy to the group.

I also went to BIDS to make sure the cube is set to TRUE for visibility.

I can see the cube in Excel, but the other guy can't. We have 3 other cubes that he can see, but just not the Test cube,

Does anyone know what I'm doing wrong?
I have following Dim Tables

FactTable :FactInvoiceDetails

My question is :

DimCustomer and DimSalesPerson having SiteID
SiteID are United Kigdom,Germany,France,USA

Dimcustomer and FactTable (FactInvoiceDetails) connected with SKCustomerKey
DimSalesPerson and FactTable (FactInvoiceDetails) connected with SKSalesPersonKey

I need to know How to connect Dimsite with DimCustomer and DimSalesPerson

But Dimsite key is connected FactInvoiceDetails not DimCustomer and DimSalesPerson
I have a blocking report (Blocking Detected On Production Server), Can anybody please help me understand that?

Here are the details:-

Session_id || Status || blocking_session_id || wait_type || wait_resource || WaitSec || cpu_time || Logical_reads || reads || writes

156 || Suspended || 137 || LCK_M_IS || OBJECT: 18:925246351:4 || 172.660000 || 7 || 204 || 66 || 0 ||

Anything more about this will be very helpful.

Thanks in Advance.
I have a primary fact table of Events with a bridge to another fact table of Interest that are tied by an Event Id. Each event can have multiple Interest. I'm able to get the correct Event & Interest counts.

The same Events fact table has another bridge to another fact table of Profiles, but this is linked by a Profile Code within the Event, instead of the Event Id. I'm able to get the correct Event counts with the correct distribution but the problem is that the Interest counts are always the same, the total sum of Interests.

The one thing I haven't tried is to include every Event Id in the Profile fact for every possible combination. I would imagine this could get huge and have a negative impact on performance.

I've attached the design and examples of correct results and of the  wrong Interest measure.
Correct ExampleMultiple Fact TablesWrong Example
I have an excel data cube (analysis services) report I'm working with . I have a report that filters a set number of part numbers from a list of about 20,000+. Based on the report requirements, I need to filter that based on a list I'm given. AS of now, the only way I know of is entering them one at a time. Very very time consuming when I have several hundred. Is there a better way? Copy and paste the list? Filter the pivot tablet based on list in another column?
Free Tool: Site Down Detector
LVL 12
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

I have invoice Header and Invoice Line Details fact Tables.
After creating Cube Invoice Header is working fine.
But Invoice Details not working properly, for example all are showing same value.(repeating)
In the invoice Header have customerID, But Invoice Details Don't have CustomerID.
Invoice Header Customer ID connected with DimCustomer,but invoiceline not connected with DimCustomer  Table.

Note: FactInvoiceHeader and FactInvoiceLine connected with Invoice Number
I tried to use Dimension Usage to connect but it's not working, Any idea how to connect with this
I'm getting following Error Message

Errors in the OLAP storage engine: A duplicate attribute key has been found when processing: Table: 'dbo_DimCustomer', Column: 'siteID', Value: '1'; Table: 'dbo_DimCustomer', Column: 'city', Value: ''. The attribute is 'City'.

SiteID - 1,2,3
City - Key Atrribute , SiteID ,City and Name conversion "City Label" (County+ city)
Country  Key Atrribute , SiteID ,Country and Name conversion "Country Label" (County)
Need help in tabular cube processing automation of Azure Analysis Service with the data source of Azure data lake.
I want to create a cube that encompasses all companies. But there is security concern about a user accessing data across companies when the users access the cube via excel etc...

Having a unified cube would greatly give us granularity across time. (some companies sell assets to other companies so this would make sense when slicing the data to get accurate numbers.

Would a feasible solution be to add a company dimension and somehow hide it..or implement some kind of partitioning?

Any general suggestions would be helpful.
I am fairly new to PowerPivot in Excel and I was wondering about how you could use two tables of unrelated data in a pivot table.  I understand about how you can relate two tables like Customer and CustomerOrder that share a common key, but what about something like this:

Claims Table:
Claim Number
Claim Type
Year of Loss
Zip Code

Population Table:
Population Count
Year of Census
Zip Code

There isn't a true key that connects these Tables but they have many fields in common.  So if I was to list Florida Hurricane claims by loss year in a Pivot table is there any way to also list the Population for Florida for the same years?  Is there a solution that would automatically switch if I changed from State = Florida to County = DUVAL?  Could this be done with a DAX expression?
For example I have the below MDX query where it gives me all the data i want. however i would like to column slice all the 20K DWT's in one column and all the 25K DWT's in another column. Could I replace all the information in the FROM columns section with something like  [Voyage].[Vessel Tag].&[LIKE %20K DWT%] , [Voyage].[Vessel Tag].&[LIKE %25K DWT%] ON COLUMNS?

SELECT NON EMPTY Hierarchize({DrilldownLevel({[Voyage].[Vessel Tag].[All]})}) ON COLUMNS ,
       NON EMPTY Hierarchize({DrilldownLevel({[Date].[Month].[All]})}) ON ROWS  
FROM (SELECT ({[Voyage].[Vessel Tag].&[FCCSP, 20K DWT, Eco, M],
               [Voyage].[Vessel Tag].&[FCCSP, 20K DWT, Eco, H],
               [Voyage].[Vessel Tag].&[FCCSP, 20K DWT, Eco, F],
               [Voyage].[Vessel Tag].&[FCCI, 20K DWT, Eco, Ma],
               [Voyage].[Vessel Tag].&[FCCI, 20K DWT, Eco, Le],
               [Voyage].[Vessel Tag].&[FCCI, 20K DWT, Eco, Ho],
               [Voyage].[Vessel Tag].&[FCCI, 20K DWT, Eco],
                     [Voyage].[Vessel Tag].&[FCCSP, 25K DWT, Eco, Marc],
               [Voyage].[Vessel Tag].&[FCCSP, 25K DWT, Eco, Ho],
               [Voyage].[Vessel Tag].&[FCCSP, 25K DWT, Eco, Fra],
               [Voyage].[Vessel Tag].&[FCCI, 25K DWT, Eco, Ma],
               [Voyage].[Vessel Tag].&[FCCI, 25K DWT, Eco, Le],
               [Voyage].[Vessel Tag].&[FCCI, 25K DWT, Eco, Hy],
               [Voyage].[Vessel Tag].&[FCCI, 25K DWT, Eco]}) ON COLUMNS  
      FROM [cube])
im Using Analysis Service for SQL Server 2014. im trying to write a simple query that cuts the amount both by Month on the row level and Vessel Tag on the column level. this get me row just fine, the second i try to cut the amount by column  [Voyage].[Vessel Tag] i get the below error. Whats the correct MDX syntax to slicing it by  [Voyage].[Vessel Tag] for column?

              NON EMPTY { ([Date].[Month].[Month].ALLMEMBERS ) }  ON ROWS
FROM ( SELECT ( { [Date].[Year].&[2017] } ) ON COLUMNS
FROM [cube]) WHERE ( [Date].[Year].&[2017] )

"The query cannot be prepared: The query must have at least one axis.
The first axis of the query should not have multiple hierarchies,
nor should it reference any dimension other than the Measures dimension..
Parameter name: mdx (MDXQueryGenerator)"
Using SQL Server 2014 and I'm trying to merge Analytic data and Relational data for a report. I'm almost there but there are parts that need to be dynamic in the MDX part.

See attached query. I need the DATASOURCE dynamic, the Initial Catalog dynamic, and the year currently hardcoded as 2017 dynamic. I try to replace them with variables and it keeps giving me errors.

any one instlaled SQL 2017 SSAS ? the full installation always show me the starting of SSAS failed.

it seems starting from SQL 2016 it has  this problem and the only way to fix SQL2016 is to add right by DBA to the SSAS folder, then uninstall SQL 2016 SSAS and reinstall it.

any idea?
Free Tool: Port Scanner
LVL 12
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.


I having little bit experience on SSIS,SSRS & SSAS. I want to get advanced knowledge on this.

Please provide some realtime scenarios with solutions?
I had this question after viewing Setup of SSAS Cube.

I am still having issues with creating a new data source.

Still looking for HELP, I AM DESPERATE. I have tried numerous approaches including the great help from Kevin Cross.  I am VERY STUCK, I cannot move forward in my training until I resolve this issue.  How do I create a new DSV when I keep getting the following error message:

"TITLE: Microsoft SQL Server Native Client 11.0

Login timeout expired
A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online.
SQL Server Network Interfaces: Error Locating Server/Instance Specified [xFFFFFFFF].


Any and All help would be greatly appreciated.


v2-native-ad-no-button.pngDo you work with and analyze data? Enroll in October’s Course of the Month to get step-by-step SQL training. Learn SQL to quickly and efficiently store and retrieve data!
We have a 2-node Windows 2008 R2 active/active SQL cluster. The SQL database is 2008 R2 but the SSAS instance is 2005 and needs to be upgraded to 2008 R2. The SSAS instance runs on it's own node with it's own clustered san drive for storing cube data etc.

We don't have a test cluster, but have upgraded SSAS 2005 to 2008 on the UAT and DEV single server environments without a problem.

We were expecting to perform the upgrade on the SSAS passive node first, then failover and make that node active and upgrade the other passive node.  As a test one of our DBAs ran the upgrade wizard on the SSAS passive node (the one where SQL database is running but SSAS isn't), expecting the wizard to identify the SSAS install. But the SSAS instance doesn't appear - it only appears in the wizard as available to upgrade on the active node.

Is this normal? What is the correct procedure to proceed with this upgrade?

We considered installing a new SSAS 2008 instance to run alongside 2005, but that seemed over-complicated: it would have a different instance name, a different drive letter and would require a new cluster config. As we can use our UAT server to temporarily create cubes in production if we break SSAS on production, we have a fallback position.

I am a newbie to SSAS and I am having difficulty setting up the Data Source, I am unable to successfully create a connection to my database for the data source.  What am I missing?

See attached for location of data files.  

How do I determine where my localhost is located?  Note this is all on my local machine, no outside servers involved.

What is my server name, do I need to include the entire filepath?

I am using Visual Studio Data Tools for 2015.

I have been tasked with moving some SQL Server reporting cubes from Server A in Datacenter A to Server B in Datacenter B and want to make sure I'm not missing anything (this is way outside my usual ball park of operations - I'm mostly an Oracle guy).

So far I have identified:

* There is a pair data warehouse databases that need moving - Simple DB backup and Restore for initial setup should work
* There are 4 SSAS Cubes.  I believe this is a backup of each cube and restore on new server
* There are several SSIS packages that need to be exported individually and re-imported into the new server
* There are several SQL Server Agent jobs that refresh these cubes that will need to be copied to the new server

Are there any other steps I need to be aware of or pieces that might need to be moved that I haven't listed above.







SQL Server Analysis Services (SSAS) is an online analytical processing (OLAP) and data mining tool in Microsoft SQL Server used as a tool by organizations to analyze and make sense of information possibly spread out across multiple databases, or in disparate tables or files. Analysis Services includes a group of OLAP and data mining capabilities and comes in two flavors - Multidimensional and Tabular.

Top Experts In