[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x

SSAS

928

Solutions

852

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 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
HTML5 and CSS3 Fundamentals
LVL 12
HTML5 and CSS3 Fundamentals

Build a website from the ground up by first learning the fundamentals of HTML5 and CSS3, the two popular programming languages used to present content online. HTML deals with fonts, colors, graphics, and hyperlinks, while CSS describes how HTML elements are to be displayed.

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
I would like to know if it can be done in MDX or DAX for checking status in every date like the following.

I can build the cube based on the following data.  I would like to count the status for id 100,101,102 and 103 in each date.
   id      event_date      status
100      01/01/2018      Pending
101      01/01/2018      Pending
102      04/02/2018      Pending
100      07/02/2018      Not Granted
101      14/02/2018      Granted
101      18/02/2018      Pending
100      15/03/2018      Pending
102      01/05/2018      Granted
103      03/06/2018      Pending

I would like to have the result like the following: (in 2018-01-01: (Pending:100,101); 2018-02-04(Pending:100,101,102); 2018-02-07(Pending:101,102, NotGranted:100 )....
event_date      Granted      Not Granted      Pending
01/01/2018                      2
04/02/2018                    3
07/02/2018             1      2
14/02/2018      1      1      1
18/02/2018             1      2
15/03/2018                    3
01/05/2018      1             2
03/06/2018      1             3

Thanks,
Adam
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
Learn SQL Server Core 2016
LVL 12
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.

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
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
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 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
OWASP: Avoiding Hacker Tricks
LVL 12
OWASP: Avoiding Hacker Tricks

Learn to build secure applications from the mindset of the hacker and avoid being exploited.

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 primary fact table of Events with a bridge to another fact table of Interest that are tied by an Event Id. Each event can have multiple Interest. I'm able to get the correct Event & Interest counts.

The same Events fact table has another bridge to another fact table of Profiles, but this is linked by a Profile Code within the Event, instead of the Event Id. I'm able to get the correct Event counts with the correct distribution but the problem is that the Interest counts are always the same, the total sum of Interests.

The one thing I haven't tried is to include every Event Id in the Profile fact for every possible combination. I would imagine this could get huge and have a negative impact on performance.

I've attached the design and examples of correct results and of the  wrong Interest measure.
Correct ExampleMultiple Fact TablesWrong Example
0
I have an excel data cube (analysis services) report I'm working with . I have a report that filters a set number of part numbers from a list of about 20,000+. Based on the report requirements, I need to filter that based on a list I'm given. AS of now, the only way I know of is entering them one at a time. Very very time consuming when I have several hundred. Is there a better way? Copy and paste the list? Filter the pivot tablet based on list in another column?
0
Hi,
I'm getting following Error Message

Errors in the OLAP storage engine: A duplicate attribute key has been found when processing: Table: 'dbo_DimCustomer', Column: 'siteID', Value: '1'; Table: 'dbo_DimCustomer', Column: 'city', Value: ''. The attribute is 'City'.

Example
SiteID - 1,2,3
City - Key Atrribute , SiteID ,City and Name conversion "City Label" (County+ city)
Country  Key Atrribute , SiteID ,Country and Name conversion "Country Label" (County)
0
Need help in tabular cube processing automation of Azure Analysis Service with the data source of Azure data lake.
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

SSAS

928

Solutions

852

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
>