Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x

SSAS

910

Solutions

828

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

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

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
State
County
Zip Code

Population Table:
Population Count
Year of Census
State
County
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?
0
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])
WHERE …
0
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?

SELECT NON EMPTY { [Measures].[TCE] } ON COLUMNS,
              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)"
0
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.
SQLMDXQuery.txt
0
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
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
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
Become an Android App Developer
LVL 11
Become an Android App Developer

Ready to kick start your career in 2018? Learn how to build an Android app in January’s Course of the Month and open the door to new opportunities.

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
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
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
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
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
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
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 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
Free Tool: Subnet Calculator
LVL 11
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

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

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

I do not have the internal resources available to me to maintain a set of OLAP data cubes I've created using SSMS and SSAS 2012.  I need someone profficient in MDX Query structure and familiar with multi-dimensional data to assist me briefly in creating the right kind of measures my client is interested in seeing.  

This is particularly around creating a total percentage calculated measure that will total to 100% for all permutations run on it within the cube.  I will be filtering dimensions, and I will be displaying dimensions within a tabular format both in columns and rows.  There are thousands of measures, and my client wants to be able to perform any combination of each.

Thank you in advance for any help or guidance you might be able to provide me.
0
Hello Experts Exchange
I am developing a Finance cube, I'm trying to mimic the same functionality as SAP BPC.

In Excel the users look at the data in the SAP BPC cube and can view and update the forecasting data.

How do I do this in a SSAS? Is there any document that explain how to do it?

Regards

SQLSearcher
0
Hello Experts Exchange
I'm new to SSAS cube development.

I have data for cost centres that are going into my cube, please see file attached.

I want to create a SSAS Hierarchy with the data.

The column PARENTH1 is the Parent folder and HIER1 is the Child folder, there can be several levels of folders.

I want to use the PARENTH1 and  HIER1 to create the Hierarchy but I want to display the EVDESCRIPTION field to the user.

How do I do this in SQL Server Analysis Services?

Regards

SQLSearcher
CostCentreFolders.xls
0
We want to uninstall AD from a Windows 2008 R2 but we want to know what could be affected.

1.-After uninstall is created a new administrator account or we can login with actual administration account just removing the @domain.com.
2.-By this way all other users are removed? We have users for ftp access and other things these users are removed?
3.-The configuration of IIs is damaged?
4.-The Sql Server 2008 and mysql accounts are affected?
5.- What other areas could be affected or changed?

We want to follow this link to uninstall:

https://technet.microsoft.com/en-us/library/cc771844(v=ws.10).aspx

We just want to know before trigger the button and cause a several damage.

Thank you
0
hi experts

i am reading about: real-time operational analytics
in https://msdn.microsoft.com/en-US/library/dn817827.aspx

whats the mean: real-time operational analytics
0

SSAS

910

Solutions

828

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
>