SSAS

924

Solutions

844

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 Experts

 

How would you edit the following formula to count only Distinct items nested within the containsrow function...

 

OHA_OHP = CALCULATE(COUNTROWS(Data),FILTER(Data,Data[CaseType]="Face to Face"), DISTINCTCOUNT(Data[WorkType]),CONTAINSROW({"IMA OHP OffSite","OHP OnSite","IMA Network OHP","OHP Review 30 Minutes", "OHP Case Teleconference call (40 mins)"},Data[WorkType]))
0
Cloud Class® Course: C++ 11 Fundamentals
LVL 12
Cloud Class® Course: C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

Problem statement: I am trying to do a writeback through .NET assembly stored procedure in SSAS - Multidimensional Cube

Are there any server level settings ? or any additional roles setting?

**Note: User is already an admin.


The .NET code works perfectly fine when debugged from the visual studio, the connection to the server works and the cube update also works.

Issue:
1) When created an assembly using the dll file from .NET class library in the SSAS server, the calling of assembly stored procedure fails and throws error as follows;
a) Assembly Permission set : Safe/External Access
"Executing the query ...
Query (1, 6) Execution of the managed stored procedure UpdateCube failed with the following error: Exception has been thrown by the target of an invocation.Request for the permission of type 'System.Data.OleDb.OleDbPermission, System.Data, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089' failed..
Run complete"
   
b) Assembly Permission set : Unrestricted
"Executing the query ...
Query (1, 6) Execution of the managed stored procedure UpdateCube failed with the following error: Exception has been thrown by the target of an invocation.An error was encountered in the transport layer.
The peer prematurely closed the connection..
Run complete"

Attached my .cs code which I build to obtain the dll file and then create an assembly on SSAS.
.Net.docx
0
I decommissioned a database analysis server and need to move a tabular  model to a new server. I open the project in Visual Studio, change the workspace server yet when I try to compile I get "cannot build projects that contain more than one .bim file". There is only one .bim file in this project. Where is 2nd one?
0
We are switching to a DevOps local source control database environment.

In SQL Server Data Tools , In an Analysis Service project,  (I note there are no pre/post build events to take advantage of) is there a way to make DataSource Dynamic?

So when we deploy the cubes it takes the Environment into account.
0
I'm trying to write a conditional sum in MDX but its being applied to the whole instead of just applying to the criteria i specified. Does anyone know what i'm doing wrong?

WITH MEMBER [Measures].[PnLCalc] AS
  CASE [Voyage].[Vessel Tag].CurrentMember
  WHEN Instr([Voyage].[Vessel Tag].CurrentMember.Name,'ACCL') > 0 AND [Date].[Year].&[2018] 
  THEN [Measures].[PnL] + (150 * [Measures].[Net Days])
  ELSE [Measures].[PnL] END , FORMAT_STRING = "$#,##0;($#,##0)"  

  
SELECT  {   [Measures].[PnLCalc], 
            [Measures].[TCE]
							} ON COLUMNS,
						{ ([Date].[Month].[Month].ALLMEMBERS  ) } ON ROWS 
					FROM ( SELECT ( { [Date].[Year].&[2018] } ) ON COLUMNS FROM 
									( SELECT ( {  
													FILTER
														(
															[Voyage].[Vessel Tag].[Vessel Tag].ALLMEMBERS,
															(
																Instr([Voyage].[Vessel Tag].CurrentMember.Name,'FCCI') > 0
																OR
																Instr([Voyage].[Vessel Tag].CurrentMember.Name,'FCCBV') > 0
																OR
																Instr([Voyage].[Vessel Tag].CurrentMember.Name,'FCCSP') > 0																								   
																OR
																Instr([Voyage].[Vessel Tag].CurrentMember.Name,'ACCL') > 0
																OR
																Instr([Voyage].[Vessel Tag].CurrentMember.Name,'ACCBV') > 0
															)
														)
												} ) ON COLUMNS 
											FROM [cubeVoyageResults])) 
							WHERE ([Voyage].[Vessel Tag].CurrentMember, [Date].[Year].&[2018])

Open in new window

0
We are using SSAS 2016 Enterprise

We are building a mega cube with multiple companies data and we want to partition by Company.
We also will want to schedule separate ETL schedules for each company.


Question is in the event there is failure from a partition perspective will that take down the entire cube? In the event the data from one company goes wonky?
0
hi,

is it possible to connection Oracle OLAP , Oracle application server, Oracle BI answer and BI server to MS SQL ?

we are thinking about what if we just migrate Oracle DB to MS SQL DB and left the rest untouched.
0
Hi, I am connecting to SSAS data cube (tabular model) from Excel. This is company's operational data. I am wondering if there are any templates I can reuse to visualize data. What is the best place to start? Any books/video suggestions, other resources?

Thank you in advance.
0
Hello ,

I have a Tabular SSAS Model cube and have a measure that hours unused  percent like Hours Unused %:=(1 - (sum([CompletedHours])/sum([HoursLeft]))) * 100

In the table, I have area ie North, South, West and East. I also have Employees per area.

When I view the data in excel and in pivot view I see the Hours unused % showing correctly when I filter by an area but when I try to add in the employees into the rows it displays all employees not just the employees in that particular area.

I'm was looking to maybe use calculate table function, but I can't seem to get it right. Can anyone assist, please?

thanks
0
When communicating with SSAS msmdpump via SOAP requests Analysis services returns with Content-Types that are compressed and in binary XML (Content_type: xs+xpress).

We would like to include an extra layer of abstraction between the client and Analysis services and would like to know if there is a way to have Analysis services send back plain XML or how to read the xs+xpress content-types.
0
Cloud Class® Course: SQL Server Core 2016
LVL 12
Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

Is there a way in SSAS 2016 and the latest version of Excel 2016 to add a value to the Description property of a Dimension Attribute and have that render as a tooltip when hovering over it in Excel?
0
Hi I have a following formula which should be implemented using MDX
UnitMarketShare=(Territory Product Units/National Product Units)*100

Dimension Geography has Territory and country ; there are multiple Members in Country attribute and each country has multiple territory (1:1 relationship between territory and country)

I have to find the total Product units(for each product) for each country and then divide it with Territory product Units. I tried the following code but i am failing to keep the National level aggregation after pulling the territory.

with member measures.abc as
sum(
[Geography].[Country].&[XYZ],[Measures].[Sales - Units])

 member measures.tabc as
([Geography].[Territory],[Measures].[Sales - Units])

member measures.ab as
([Measures].[Sales - Units]/measures.abc)

select [Geography].[Territory].[Territory] on 1,
{measures.abc,measures.ab,measures.tabc,[Measures].[Sales - Units]} on 0
from [CLG_MultiSourceCube]
where [Geography].[Country].&[XYZ]

Following is the sample dummy data with desired output

Country    Territory   SalesUnits   TerritorySales   NationalSales   UnitMarketShare
XYZ              T1                  100                  100                 350                      28.57
XYZ              T2                  200                  200                 350                      57.14
XYZ              T3                  50                      50                 350                      14.28
ABC             T4   …
0
I have an excel 2016 pivot table that is using a data connection to an ssas 2012 Olap cube

When I try to add a timeline slicer I get the dreaded

We can't create a timeline for this report because it doesn't have a field formatted as a date

I do have an order date attribute that is defined as a date field on the dimCalendar table

I have done some research and found some possible causes

one possible issue mentioned is that the field may have blank or invalid dates.
(I double checked this is not the case in my data)

The other was that the field needed to have the

ValueColumn of the date key to use an actual date field from the underlying SQL table.  The KeyColumn is an integer and the NameColumn is WChar.  and to set the ValueColumn to use type Date

I validated this was true as well

Anyone have some other thoughts
0
The attribute key cannot be found when processing: Table: 'dbo_FACT_PROD_QTY', Column: 'CONTRACT_RATE_KEY', Value: '0'. The attribute is 'CONTRACT_RATE_KEY'.
The attribute key was converted to an unknown member because the attribute key was not found. Attribute CONTRACT_RATE_KEY of Dimension: ContractRateLevel f

- I can process the dimension (ContractRateLevel), that the Fact table is pointing to with no problem.
- The dbo_Fact_Prod_Qty table does have Nulls in column Contract_Rate_Key.
- There are no Null values in the Dimension table: ContractRateLevel.

Can someone help, as I don't have this issue with my test cube, and the Fact table as NULL values for the same column.
0
I have an existing SSAS muti-dimensional data cube I'm trying to add another calculated member to.  I added a "parallelperiod" and that MDX query is working well.

(ParallelPeriod([Date].[FY-Hierarchy].[Fiscal Year],1,[Date].[FY-Hierarchy].currentmember),[Measures].[reseller sales amount])

Open in new window


My goal is to get the same calcualtion for Year-to-date(YTD). I'm trying to plug in YTD into that and I get #value! in excel.

(YTD(ParallelPeriod([Date].[FY-Hierarchy].[Fiscal Year],1,[Date].[FY-Hierarchy].currentmember)),[Measures].[reseller sales amount])

Open in new window


Any ideas on how to get YTD to work?
0
Hi Sir,

How to find  attribute name,level name and hierarchy name  of olap database usin AMO?


Thanks
Sandip
0
Hi Sir, I want to retrieve all prespective with in cube getting the error due to microsoft.analysisservices.prective is not getting.

Please help me.


Thanks
Sandip
0
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?
0
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.
0
Cloud Class® Course: Microsoft Azure 2017
LVL 12
Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

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
0
All,

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?
0
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. (http://olap.mycubes.com)

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?
0
All,

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?
0
I have following Dim Tables
DimCustomer,DimSalesPerson..ect

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

SSAS

924

Solutions

844

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
>