SSAS

935

Solutions

862

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 try to make the HTTP connection Http access to SSAS (MS Analysis Services) server over my IIS server

*i used guide https://docs.microsoft.com/en-us/sql/analysis-services/instances/configure-http-access-to-analysis-services-on-iis-8-0

When I try to make the connection from Microsoft SQL server Managment Studio over conn string: http://localhost/olap/msmdpump.dll

error raised: The remote server returned an error (405) Method Not Allowed. (System) i didnt find a solution
how to solve this problem?
0
Announcing the Winners!
LVL 19
Announcing the Winners!

The results are in for the 15th Annual Expert Awards! Congratulations to the winners, and thank you to everyone who participated in the nominations. We are so grateful for the valuable contributions experts make on a daily basis. Click to read more about this year’s recipients!

I have end users that have previously used cubes within the organisation and when hitting equals and pointing towards one of the totals within a pivot table had a getpivotdata formula assembled.  

However previously the formula referenced the Item Code field by its description, however it now uses the surrogate key, which makes customising the getpivtotable formula impossible for end users who don't (and shouldn't) know the surrogate keys we use.

The only option i can think of is to change the key to be the business key as opposed to the surrogate key, however that goes completely in the face of the cube design considerations I have always followed.  Also end users are saying this wasn't the case with the old cube they had access to, I am aware this could be an issue with the later version of Excel/SSAS or it could be a setting in Excel/SSAS that I could change, but haven't had any luck locating it yet.
0
When working on an SSAS cube within Excel I added one field (product category) as a filter and set it to a particular value, I the drag in another field (product name) into the rows and see 8 rows rendered in the pivot table.  

However when clicking on the drop down for row labels, the filters I see for the second field (product name) lists all of the products without taking into account the filter I have applied on the table (on product category).  

Is there anyway to get this to behave more logically, as I wouldn't expect it to show all values in the whole cube when the end user has set the the filter on table pivot table.
0
Dear Experts
We are using CRM application which uses MYSQL database. we have customized the CRM application for our core process therefore the CRM modules marketing, sales and contacts and opportunities are not made use instead we have completely customized, the solution is deployed on premises.
Challenges: to build/develop certain reports which are very much essential for daily operations and monthly reports and analytics. At present we are taking csv dump from crm and preparing it manually by applying formulas,  this is time consuming and resource dependent and as reports are not ready made available therefore end users to depend on the technical resource to export the reports and bring it to the required format and then provided to the end users. We also require analytics from the CRM data.
We are planning to implement SQL Server Reporting Services (SSRS) and integrate with Power BI for analytics the data source will be CRM application either integrated OR excel as input to the SQL Server Reporting Services (SSRS) or Power BI.
Please suggest will this approach work for us.
0
I have a very large table with 1 billion rows
One of the columns stores full address details (street, town, city, country)

Previously it was varchar(max) but since we moved to multilingual we have to change it to nvarchar(max) which doubles the size

Our database size is now growing by 10.5GB a day and I'm looking at ways to reduce this
Is there a way to compress this data and uncompress on the web app?

Open to ideas and suggestions

SQL Server 2017 enterprise
0
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
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
Learn SQL Server Core 2016
LVL 19
Learn 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.

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
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
Python 3 Fundamentals
LVL 19
Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

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

SSAS

935

Solutions

862

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
>