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

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

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.

need to to understand the physical structure of an OLAP database. And if I looked into a Oracle database, how can I tell if it's relational or OLAP? Isn't the OLAP data still stored in a table with keys that join to the other tables? I'm having a hard time picturing the structure and then I know querying is different too.

For years I heard these data warehouse jockeys talking about cubes, dimensions, and measures, craftily convincing the IT managers they know what they are talking about, and impressing the users that "this is the thing." But it always confused me. So I watched a few videos today, and the light bulb goes off - "oh, it's a pivot table".

btw, the cube is a nifty visual for a powerpoint presentation, but it's still presented in two-dimensions (piece of paper or a computer screen). Unless you have a 3-D printer I suppose ...

So I got the concept of measures, dimensions, etc., but now need to "get the picture" in what the data actually looks like and how to query it. I know this is bigger than one post here, but that's my objective.

For example, here's my confusion - I was also told today that an OLAP database is NOT relational in essence, it's totally different. In this article, you have
- the time dimension
- the product dimension
- the customer dimension
- the Fact table (total $ sales, quantity, discount)

BUT - don't all the dimension have keys that link it to the fact …

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

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!

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

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. Attached a sql for creating the table.event_sql.txt
   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

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

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.

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

Select all Open in new window

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.

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