SSAS

933

Solutions

856

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

In C# I'm trying to add a new dynamically created partition to and existing Aggregation Design.

If it is done statically in Visual Studio then deployed there is no problem. But when new companies are added I wrote a script task in the ETL to setup everything on the fly since the multi tenant cube is partitioned by Company.

It seems when done dynamically it is trying to create a schema bound indexed view of sums under the hood and since the underlying facts and dimensions are views themselves this fails.

Question is why is it trying to created this new hidden view. And why when the partitions are added in visual studio to the aggregation design prior to deployment i get no such error?

This is my C# code
                        //If Partition isnt found it needs to be created dynamically. Both the Archive and Current.
                        if (!lbFoundPartition)
                        {

                            //Create the new Archive Partition

                            oNewPartitionArchive = oMeasureGroup.Partitions.Add(PartitionNameArchive);
                            oNewPartitionArchive.StorageMode = StorageMode.Molap;
                            oNewPartitionArchive.Source = new QueryBinding(oDB.DataSources[0].ID, PartitionQueryArchive);

                            if (oMeasureGroup.AggregationDesigns.Count > 0)
                            {
                                oNewPartitionArchive.AggregationDesignID = 

Open in new window

0
CompTIA Security+
LVL 13
CompTIA Security+

Learn the essential functions of CompTIA Security+, which establishes the core knowledge required of any cybersecurity role and leads professionals into intermediate-level cybersecurity jobs.

SSAS - Cube Processing - Failing with the following error.

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

Open in new window


The error is originating from one of the Dimension. This dimension is based off of a fact table (instead of a dimension table). Therefore, duplicates always exists for fields such as city, state etc.

The cube was processing successfully for a long time and failing all of a sudden. The above duplicate error appears to be misleading.
0
When creating an SSAS project in SSDT (Visual Studio), how do you get SSDT / project to remember the 'Impersonate Account' user details?
Currently, every time i open a project and make a change, i have to enter the username and password details for the Impersonation Account of each data source, before i can deploy the project. This is particularly irksome, as my main project pulls data from 20 different sources!
I am using SSDT 2017 v 15.9.9
Any help gratefully received.
I am a relative beginner in using SSAS, so please excuse me if this issue is a lack of basic knowledge.
0
I have a SSRS Pick List report for my shipping department. It is time consuming to print one at a time. I was tasked in creating a batch to generate a single PDF and the user only needs to send the print job once. I successfully created this data driven report. The only issue is that I get 100(example) emails with one pick report in each. I need one email with one PDF containing all  pages. Is it possible to get this in one file instead of individual ones? I'm using SSRS 2017.
0
I've written a DAX measure to give me number of cases that match a lookup of certain base date.

My dax measure is in the DaxMeasure.PNG file.

It works mostly, but I'm having difficulties showing me the total when I filter by certain date.

When I filer by the date 20.03.2019 it gives me the desired result, that is, all the rows that this date falls between, and the grand total that is distinct count of case SQN number (file FilterScenario1.png)

When I on the other hand filter by another date, 19.03.2019 it gives me the desired result, but there is no Grand total showing (file FilterScenario2.png)
(I've tried both Excel and Power BI and it's giving me the same result.)

Any idea what is causing this, is it the maybe the blanks in my DAX query or something else, any help appreciated.

Cheers,
Gosi
DaxMeasure.PNG
FilterScenario1.png
FilterScenario2.png
0
I'm using SSAS 2016 Enterprise.

I'm using a multi-tenant cube that includes 40 companies with partitions on the fact tables. For 35 of these companies all runs fine. but 5 of them are giving me an issue. I am getting the below error but the windows login is the same for all users in a company. The security is setup this way so when the each user accesses their data via Excel, they only see their data. Hence the design.

It works perfectly fine for most of the companies. I don't understand why for these particular 5 companies whenever they are introduced into the ETL process I get these errors. They are are set up the same.


Here's my security expression

NonEmpty (
[Vessel].[VesselSID].[VesselSID].Members,
(
StrToMember ("[User].[Windows Login].&[" + UserName () + "]"),
[Measures].[Count - Bridge Vessel]
)
)


My gut feeling is that there is a design flaw somewhere that these companies are susceptible to. The cube is massive and the security is the same for all the dimensions.

I don't feel this is the "real" error. Is there a cube design expert out there that can shed some light on what might be causing this?


Dimension Design LayoutDuplicate Key ErrorSecurity Expression Error
0
I'm having an issue with a DAX formula in SSAS/Tabular.

I have a calculated measure (rtn_qty_adj) which provides the values as expected, however when I reference that formula in another calculated measure (rtn_qty_avg_adj), it gives incorrect values.
It appears that "rtn_qty_avg_adj" is ignoring or filtering-out the logic of the calculated measure "rtn_qty_adj".

Basically the dataset consists of returns quantities over a (monthly) timeframe.
Where there are big peaks/excursions in the data (ie; return qty. in a month is greater than uper control limit) , then in the measure  "rtn_qty_adj" I reset these peaks to the Average return qty.
So then I want to perform another averaging on the adjusted returns qtys. where the peaks are "smoothed-out" (ie; equated to the average)
Most of the calculations work just fine, except this one which is causing me a headache.

Appreciate all input/help!!

Formulae
Here's the formulae for the adjusted return qty and adjusted return qty. average
Rtn_qty_adj:=
VAR rtn_avg =
CALCULATE(
		(AVERAGEX(
			SUMMARIZE( drct_excg, dates[incr_base_date], "rtn_avg",  [Rtn_qty] )
		,[rtn_avg] ) )
, ALL(dates))
RETURN
IF( [Rtn_qty_ucl_1sd_otlr] = 1, 
	IF(COUNTROWS(VALUES(dates[incr_base_date])) >1,
		SUMX(VALUES(dates[incr_base_date]),
			IF( [Rtn_qty_ucl_1sd_otlr] = 1, rtn_avg , [Rtn_qty] )  )
	,
	rtn_avg
),
[Rtn_qty]
)

Rtn_qty_avg_adj:=
CALCULATE(
	AVERAGEX(
		SUMMARIZE( drct_excg, dates[incr_base_date] ,"rtn_avg", 

Open in new window

0
I am newbie to SSAS. I created the DSV, a few dimensions and a cube. Once I deployed the cube, it got so many error message. I assume that there might be some configuration I did not do properly. Please advice! Thank you!

Capture.PNG
0
Hi All

I have a Time Dimension in my SSAS that i need to extend, i have followed the following Article

https://social.technet.microsoft.com/wiki/contents/articles/24675.extensionexpansion-of-time-dimension-in-analysis-services.aspx#Expansion_technique_for_time_dimension_created_using_time_table_generated_in_the_data_Source

but it does nopt seem to work can anybody assist
0
While using SSAS 2016 I have inherited a Multi-Tenant Cube.

This cube contains data across all companies.

How ever when you access the cube via excel using an assigned company level windows login that exists on the user dimension...its smart enough to only bring back data for that company.

My question is where does that logic exist that makes Excel smart? How does it know? We have no proxy site set up yet. Just a site where the mspump sits.
0
Why Diversity in Tech Matters
LVL 13
Why Diversity in Tech Matters

Kesha Williams, certified professional and software developer, explores the imbalance of diversity in the world of technology -- especially when it comes to hiring women. She showcases ways she's making a difference through the Colors of STEM program.

I am trying to create SSAS tabular model. I have a stored procedure which i can pass parameters which is suppose to pull in some result set.

I go to visual studio>Model>Import from Data Source>Microsoft SQL Server. I put in the servername where the database is and use windows authentication and provider the credentials. Then i select "Write a query that will specify the data to import" and i put the stored procedure with the parameter. It validates fine but at the table import wizard i get the error which i have attatched.

Its wierd because if i just use a test proc where i pass in a parameter(integer) and it returns a date. This works and i get the data set.

So i am not sure if it is this proc related. I read somewhere it may be because of the use of CTEs. So i changed the proc to use physical tables instead of CTEs, still i am getting the error. I also used set no count on. This also did not help.
error.JPG
0
Hi Team,

Could you Process walk me through steps in processing the SSAS Cube (standard edition)  having multiple partitions(>3) for each measure group . Multiple partitions are required as it would impact the processing time in production having large volumes of data . The plan is to have the partitions defined on each Calendar Month. we want to have atleast 2 years of data . So 24  partitions for each measure group.  This is achievable using enterprise edition but I don't know how to achieve the same using standard edition .

Please Note : We cannot use the enterprise edition as it costs 4 times to the standard edition cost  and the objective is to have a workaround (working solution) to process the cube (with partitions using standard edition).

Please help me with the solution and will appreciate your quick reply .

Thanks,
Srk.
0
Hi,
 Please find attached Image.
My query
I have Created Data warehouse. For example
Dimension Table : DimSite, DimCustomer,DimSalesperson
FactTable : FactinvoiceDetails

DimSite is Include

SiteID    Sitename

1            United Kigdom
2           USA
3           France

DimCustomer
SiteID       CustomerID CustomerName
1                R001             Ryan
2                R001             Bryan
3                R001            Stephen

DimSalesperson
SiteID       SalespersonID      SalespersonName
1                S001                       Mark
2                S001                       Stacy
3                S001                       Franck


My question is

I have to connect DimCustomer and DimSalesPerson to DimSite Table also
FactTables.png
0
I have Write the DAX Function
See below
TotalDays = DATEDIFF([ChangeDate],TODAY(),DAY)
But same date giving 2 differents values
See attached Screenshot and see example
Change Date   TotalDays
12/09/2018     10
12/09/2018      2
Any idea appriciated
DAX-Issue.JPG
0
My company recently signed up for Azure SSAS and I'm testing out a few aspects of it.  I created a tabular cube that has 6,000,000 rows both in our on prem SSAS server and in Azure.  The on prem cube performs great.  The Azure cube does not.  A simple query to count the number of rows takes over 3 min in Azure vs. instantly in the on prem server.  We currently have the Basic B1 pricing scale which I've read should be more than sufficient for our needs.  Does anyone have any advice here on what I'm doing wrong?  It seems like this should be an issue easily addressed.
Thanks!
0
All,

Below is screenshot of my dimension hierarchy and the browser window of the hierarchy. As you can see in the browser of the dimension in the cube, you can see all of the dates.

However, when I access this info in Excel, I can only see the latest date, which is 8-20-2018.

Dimension Hierarchy

Dimension Browser
0
I have a SQL task called TransferInfo inside of a Container with other SQL tasks.

Every now and then, it appears that only the TransferInfo task runs twice. The other SQL tasks in the same container only runs once.

Please advise!!!
0
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
OWASP Proactive Controls
LVL 13
OWASP Proactive Controls

Learn the most important control and control categories that every architect and developer should include in their projects.

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

SSAS

933

Solutions

856

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
>