Go Premium for a chance to win a PS4. Enter to Win

x

SSAS

904

Solutions

825

Contributors

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

hi,

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?
0
Free Tool: ZipGrep
LVL 11
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Hi,

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

Please provide some realtime scenarios with solutions?
0
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].

------------------------------
BUTTONS:

&Retry
Cancel
------------------------------
Any and All help would be greatly appreciated.

Thanks,

Karen
0
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!
0
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.

Thanks!
0
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.


Setup_AWDW.JPGFilepathAWDW.JPGErrorMsg_SSAS.png
0
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.

Thanks
0
Hi
When I try to create a ssas tabular project in ssdt I get the following error.

You are not an Administrator on the workspace database server (Servername).

Any suggestions?
0
In PowerShell ISE, running modified code to create partitions programmatically from a good example, but said example doesn't work.
$as = New-Object Microsoft.AnalysisServices.Server
   $as.Connect($AS_SERVER)
    
   # get the database
   $db = $as.Databases.FindByName($AS_DATABASE)
   write-host ("Opened database {0} " -f $db.ToString())

   $model = $db.Model
   write-host ("Found model {0} " -f $model.ToString())

Open in new window


The connection to the database works fine. But $db.Model doesn't find an object. According to the directions, I'm running this in SQLPS, which I presume makes it unnecessary to add a reference to the Microsoft.AnalysisServices.Tabular assembly but is otherwise the same. When I run a similar code in ISE with the assembly reference, I do reach the model, but then I'm stymied by a DataSourceID I cannot find.
0
how do I join two queries, join them HORIZONTALLY, i.e. extra columns, second columns query 2 to right of first query



--query 1 OUTPUTS
SELECT a.timeStampKey, t.timeStamp,MAX(CASE WHEN a.PIE2_O_ID = 1 THEN value END) AS 'K_DESIGN_SG_A_AVERAGE_A', MAX(CASE WHEN a.PIE2_O_ID = 2 THEN value END) AS 'K_DESIGN_SG_B_AVERAGE_B'FROM   tblOutputs as a INNER JOIN       tblTimeStamp as t ON a.timeStampKey = t.timeStampKey GROUP BY a.timeStampKey, t.timeStamp
--query 2 INPUTS
SELECT a.timeStampKey, t.timeStamp,MAX(CASE WHEN a.PIE2_I_ID = 10104 THEN value END) AS Flow, MAX(CASE WHEN a.PIE2_I_ID = 10006 THEN value END) AS Head FROM   tblInputs as a INNER JOIN       tblTimeStamp as t ON a.timeStampKey = t.timeStampKey GROUP BY a.timeStampKey, t.timeStamp
0
Free Tool: Site Down Detector
LVL 11
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.

Hi ,

Please provide detailed procedure for In-Place Up-gradation from MS SQL Server 2012 to MS SQL Server 2014 include MSBI Components like SSIS Packages, Cubes/OLAP Databases and SSRS Reports.

Please consider this high priority.

Thanks,
Chandra
0
I'm new with SQL Server, but am pretty skilled with BI tools using Power Query & Power Pivot and such.  To learn more about the upward limits of Power Query and to get to know more about SQL Server Analytical Services (SSAS), the Tablular model and SQL Server Data Tools (SSDT), I started a self-learning project using a large CSV dataset from the US Dept of Ed, with 1900+ columns, 98000+ rows (dataset is publicly available).   I found Power Query does a nicer job of parsing raw CSV data into correct columns than the SQL Server import/export wizard, and has fewer constraints.  For example, Power Query can handle sets with > 1000 columns.  Most of the USDeptofEd data columns contain values that can be unpivoted; doing so creates a file with 18 columns but with over 185 million rows!  So it seems best to load this data into a proper SQL Server database with tables.  Of course, to get SSAS to accept Power Pivot data model I had to split the tables into 2 so none had more than 1000 columns - which I did.  I successfully imported the data into a SSAS Tabular solution using suggestions at this this OakTree post.  This was all done on a i5-2320 3GHz 64 bit processor running on Windows 10 with lots of unused storage, but 8GB of RAM.  

Here are a few screen shots so you can see what's going on:Power QueryPower Pivot - data modelSQL Server Data Tools in Visual StudioPower QueryPower Pivot - Data ModelVisual Studio
Questions:
1. Can anyone tell if my data is optimally stored in SQL Server? …
0
Users get error 'operation was cancelled because of locking conflicts' in excel, when cube is processing, they can access the report from excel or Sharepoint. I need to show custom messages from cube itself. any suggestions please In general how can I replace the system generated message with custom message
0
Hello

I have a simple MDX cross join that produces the following result

select crossjoin (   {[Product].children}  ,{ [Measures].[Actual], [Measures].[Plan] }  )
ON Columns,
[Region].children on Rows
From [cube]

eg product 1   product 1     product 2  product 2
     Actual         Plan              Actual        Plan

crossjoin result
I want to combine the member names from the 2 column dimensions into one name, to look like a relational grid
eg product 1 - Actual    product 1 - Plan       product 2 - Actual    product 2 - Plan

Flattened Result
The Products are dynamic - the Measures can be hard coded.

Has anyone got a head start on how I may be able to produce the flattened output?

Thanks
John
0
Hello ,

We have a report that displays a data dictionary of the SSAS Cube and we have description fileds missing. I wanted to enable users to update the description for say the Measures for certain tables.

Is there any way to do this with excel ? or is there another free tool that will allow them (certain permissions only) to update or generate a script to update the say $SYSTEM.MDSCHEMA_MEASURES ?

Thanks in advance.
0
I have a DSV that is made up of my Fact table and Dimension tables. Im kinda new to this(I was able to do this years ago). I remember being able to process a cube and browse the data in my tables. I know I will be missing a lot of pieces of code but for now I am just trying to give my boss a proof of concept. Which means if I can somehow show him the data and the ability that SSAS has to save time on reporting rather than write ad-hoc reports than I can go back and do this the correct way. Like I said, I just have to be able to browse the data that consists of the data in the Dimensions shown below. My problem is I cannot Process the cube without getting errors. I have a small star schema consisting of a Fact table that is loaded and the Dimension tables that hold the lookup data. All I want to do at this point is show the data By Carrier Name with the TXN_01-TXN_15(Column fields are defined in another lookup right now) aggregated. Literally that is all I need at this point. Can someone help me with this? The pic embedded is my data source view and when I try to Process the cube with the suggested files I get the following errors.....

 Star Schema DSV
Again,I know I am not doing this correclty I just need to be able to show the data so I can rewrite this the correct way and trust me I will be coming back to this site for more help!!

The following is  what I want to be able to show my manager with the data I have in my Dimensions...Sorry for the editing I just …
0
Hi Experts ,

  Issue : Linked Server is being created but no catalogs are found when accessing the SSMS (Sql Server Management Studio) with SQL Authentication but with Windows Authentication , I can access the catalogs.

Linked Server

Please Note : WH DB Server /Cube Server / Linked Server are on the same machine.

Here is the code , how I am trying to create a linked server and catalogs .

Please help me with the solution , will appreciate your help in this regard.


if NOT exists (select srvname from master.dbo.sysservers where srvname = 'RPM_Cubes')
BEGIN
Exec master.dbo.sp_addlinkedserver
@server = N'RPM_Cubes',
@srvproduct ='',
@PROVIDER = N'MSOLAP',
@datasrc=N'localhost\sqlserver2016',
@catalog ='RPM_Database'
EXEC master.dbo.sp_serveroption @server=N'RPM_Cubes', @optname=N'rpc', @optvalue=N'true'
EXEC master.dbo.sp_serveroption @server=N'RPM_Cubes', @optname=N'rpc out', @optvalue=N'true'
END
ELSE
 PRINT 'Link Server already exists'


Thanks,
SRK.
0
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes. We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that with a PowerShell script.
0
Hi Experts ,

  Issue : Linked Server is being created but no catalogs are found. I am trying to access the SSAS Cubes using the  MSOLAP provider as mentioned below.
Please Note : WH DB Server /Cube Server / Linked Server are on the same machine.

Here is the code , how I am trying to create a linked server. Please let me know if I have to configure any server options to create the catalogs in linked server.

if NOT exists (select srvname from master.dbo.sysservers where srvname = 'Test_Cubes')
BEGIN
Exec master.dbo.sp_addlinkedserver
@server = N'Test_Cubes',
@srvproduct ='',
@PROVIDER = N'MSOLAP',
@datasrc=N'localhost\sqlserver2016',
@catalog ='Test_Database'
EXEC master.dbo.sp_serveroption @server=N'Test_Cubes', @optname=N'rpc', @optvalue=N'true'
EXEC master.dbo.sp_serveroption @server=N'Test_Cubes', @optname=N'rpc out', @optvalue=N'true'
END
ELSE
 PRINT 'Link already exists'


Thanks,
SRK.
0
Hire Technology Freelancers with Gigs
LVL 11
Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

One of the developers here created this process in Oracle that ends up being a query that references 5 tables with joins. What I did was create an SSIS package to load the data into SQL tables. I then created another package to load Dimension tables based off of these tables .

Im currently at the point where I need to load the Fact table. Here is a pic of my tables



I have been doing some research and I found a good example of loading my Fact table and Im having trouble trying to figure out what my first steps are. I realize that I need to do Lookup to get Dimension info but in this example
Demo Im trying to follow

Im having trouble trying to follow the logic in the Fact table Load. The author is starting off using an OleDB Source(Hire table) to get the SnapshotDateKey, which I kinda understand, but he is using a source from an OLTP table? I dont know what I would need to do in my process and if so how would I customize it for my needs. It is throwing me off big time..

Its been a while so if there is something that doesnt seem like it belongs please let me know....

Thanks for your help. I really need to figure this out and wanted to see what developers, who know what they are doing, think about what Ive done so far....
0
Hi I have a cube that I have setup a Report Action on that opens an SSRS report that contains a parameter of @JobNumb.  The SSRS Report is based on a database, NOT a cube.  I can get the Action to appear on the menu when I test the cube in Excel and open the report is SSRS, however I am struggling to pass the parameter over!  I have tried several variations and simply cannot get it to work.

The Parameter Name in SSAS is the same as the Parameter Name is SSRS.
The SSRS Server Details are correct in the SSAS Reporting Action as it opens the report successfully.
If I type in the job number itself as the Parameter Value, eg "170000007" then the Report runs successfully with the correct job no.

I can seem to find lots on passing parameters from a cube to an SSRS Report based ON a cube, but nothing about passing a parameter from a cube to an SSRS Report based ON a database as I think it makes a difference.

I am not good with MDX and have tried the following variations as the Parameter Value, some do return a value in the SSRS Report Job Parameter field (those with a YES) but the majority don't:


"170000007"      yes      works and the report runs successfully with the correct job no.

[Job Trades].[Job Trade].[Job No].&[170000007]      no      
[Job Trades].[Job Trade].[Job No].CurrentMember      no      
[Job Trades].[Job Trade].[Job No].CurrentMember.Name      no      
[Job Trades].[Job Trade].[Job No].CurrentMember.UniqueName      no      
[Job Trades].[Job Trade].[Job No].CurrentMember.Value      no      
0
hi experts
i am reading about Working with Data Source Views in sql server data tools
i do not understand, As the creation of a relationship can improve performance, do you have any examples?

Create relationships to improve performance
0
Hi we have an SQL Server with a VLK.

We have some problem with the server and to start up the services.

I says in the log:
2017-04-25 10:01:05.92 Server SQL Server evaluation period has expired.
2017-04-25 10:01:05.92 Server SQL Server shutdown has been initiated

But that doesn't makes sense do to the fact that it is a volume license key we used.

is there anyway i can fix this?
0
I just installed 2016 SSAS. I am trying to deploy my cube but I keep getting this error. In 2008 there was windows service you could stop and for Analysis service. I dont see that service with the 2016 version. I am running Windows 2012. Any advice on how and where I start this service?


Severity	Code	Description	Project	File	Line
Error		The project could not be deployed to the 'localhost' server because of the following connectivity problems :
A connection cannot be made. Ensure that the server is running.
To verify or update the name of the target server, right-click on the project in Solution Explorer, select Project Properties, click on the Deployment tab, and then enter the name of the server.			0

Open in new window

0
I have a mobile report with one SSAS (MDX) dataset that has a parameter for the "Region". When I test the URL containing the parameter query string 2 of the 16 Regions don't populate any data on the mobile report. In SSMS and in data preview of the mobile report publisher ALL regions have data.

I've already checked the dataset for filters and I have none. Any ideas on what would cause certain MDX members not to pull up data?

Thank you!
0

SSAS

904

Solutions

825

Contributors

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