Logon failure: unknown user name or bad password - SSAS 2012

Error Message

Internal error: The operation terminated unsuccessfully.

The following system error occurred:  Logon failure: unknown user name or bad password.

Errors in the high-level relational engine. A connection could not be made to the data source with the DataSourceID of 'Adventure Works DW2012', Name of 'Adventure Works DW2012'.

Description

This error occurred when process a cube, this is happening while process only, the cube can be deployed successfully. There can be many reasons why “a connection could not be made to the data source”, but the connection parameter all varied and all fine. This issue was happened in SQL Server 2012 environment.

Solution

Reason was Set Impersonation Options not set.

When creating a data source object in an Analysis Service model, one of the settings that you must configure is an impersonation option. This option determines whether Analysis Service assumes the identity of a specific Windows user account when performing operational related to connection, such loading an OLE DB data provider or resolving user profile information in environments that support roaming profiles.

(More info http://technet.microsoft.com/en-us/library/ms187597.aspx)

Set impersonation options in SQL Server Data Tools

1. Double-click a data source in Solution Explorer to open Data Source Designer.
2. Click the Impersonation Information tab in Data Source Designer.
3. Choose an option described in Impersonation options in this topic.

Set impersonation options in Management Studio

In Management Studio, open the Impersonation Information dialog box by clicking the ellipsis (...) button for the following properties of these dialog boxes:

1. Database Properties dialog box, through the Data Source Impersonation Info property.
2. Data Source Properties dialog box, through the Impersonation Info property.
3. Assembly Properties dialog box, through the Impersonation Info property.

Cheers!

How to round a MDX value

In most of the case, you may want to perform a round operation. The one of the following ways can use for this.

Cheers!

MDX query to retrieve Dimension member properties (Unique_Name and MEMBER_KEY)

Analysis Services exposes intrinsic properties on dimension members that you can include in a query to return additional data or metadata for use in a custom application, or to assist in model investigations or construction. In here I am going to explain how to retrieve the properties through the SQL Server Management Studio (SSMS) and MDX.

In below simple MDX query retrieve all the members of Employee Hierarchy, when double click on any the member, Member Properties window will pop up. In this window some of the member properties will be display as shown below.

If we want to retire all the member properties, properties need to include in the MDX query.



To get through the MDX query, the following query format can be used.

Cheers!

How to: Create an Analysis Services Project Based on an Existing Analysis Services Database

In SQL Server Data Tools (SSDT), click New Project. In the New Project dialog box, in the Installed Template pane, select Business Intelligence Projects, and then select Analysis Services.

Select “Import from Server” and follow the steps as show in the below screen shots.

Select SSA Server and Database, then click next, new project will be created with existing objects in the SSAS Database.

Cheers!

Dynamic cell formatting in SSRS

This post is about, how to change cell formatting dynamically in reports. Even though there is no any particular property, this can be done using “Conditional Formatting”.

The Iif function returns one of two values depending on whether the expression is true or not. The following expression uses the Iif function to return a Boolean value of True if the length of Description not exceeds 50. Otherwise it returns False. If function rerun true then font size will be 10pt, else 8pt.

A different way to get the same functionality uses the Switch function. The Switch function is useful when you have three or more conditions to test. The Switch function returns the value associated with the first expression in a series that evaluates to true.

In the above example I have shown only formatting the font size, the same formatting expression can be applied for color changing, font style and many more.

Cheers!

ORDER BY - T-SQL / SQL in SQL Server

This post is about ORDER BY function and its different functionality.

1. TOP n or Top (n)

2. TOP (n) PERCENT

3. WITH TIES

4.OFFSET-FETCH

TOP n or Top (n): This will return top n records.

TOP (n) PERCENT: This will return n% of the records, for example the below example shows that total record 830, so 1 % of 830 ~ 9 records.

This example shows that how to use TOP function dynamically: TOP (@n)

WITH TIES: It will retrieve the TOP (n) records + get the same order by field records as well:

In this 2nd query, even we try to retrieve top 3 records; third records order date is same as 4th records so it returns that as well. But, in third query Order By (order date and order id), 3rd and 4th records are not same order id, so only 3 records returns.

Filtering Data with OFFSET-FETCH:

Skipping capability, in this below query 2 record skip and take next 4 records

Cheers!

Correlated Subqueries

Correlated subqueries are subqueries where the inner query has refer a reference to a column from in the outer query.

As an example, suppose that you need to return products with the minimum unit price per category.

As another example of a correlated subquery, the following query returns customers who placed orders on particular day using EXISTS.

We can use NOT EXISTS also:

Cheers!

ORDER BY (OFFSET-FETCH and WITH TIES) - SQL 2012

This post is about ORDER BY and the relevant functions: In here I don’t write the details in the text because the screen shots will make clear all.

TOP 3 or Top (3)

TOP (1) PERCENT

TOP (@n)

WITH TIES: It will retrieve the TOP (n) records + get the same order by field records as well:

In this 2nd query, even we try to retrieve top 3 records; third records order date is same as 4th records so it takes that as well.

But, in third query Order By (order date and order id), 3rd and 4th records are not same order id, so only 3 records retrieved.

Filtering Data with OFFSET-FETCH:

Skipping capability, In this below query 2 record skip and take next 4 records.

Practicable example for OFFSET-FETCH:

you’re implementing a paging concept where you return to the user one page of rows at a time. The user passes as input parameters to your procedure or a function the page number they are after (@pagenum parameter) and page size (@pagesize parameter). This means that you need to skip as many rows as @pagenum minus one times @pagesize, and fetch the next @pagesize rows. This can be implemented using the following code (using local variables for simplicity).

Cheers!

LIKE Function

The table provides some additional functionality of LIKE function

Wildcard	Meaning	Example
% (percent sign)	Any string including an empty one	‘D%’ : string starting with D
_ (underscore)	A single character	‘_D%’ : string where second character is D
[<character list>]	A single character from a list	‘[AC]%’ :  string where first character is A or C
<character range>]	A single character from a range	‘[0-9]%’ : string where first character is a digit
<^<character list or range>]	A single character that is not in the list of range	‘[^0-9]%’ : string where first character is not a digit

If you want to look for a character that is considered a wildcard, use the escape ‘!’ character before the wildcard character.

Ex:

‘!_%’ à starting from _

‘!*%’à starting *

How to use app.config (Windows Form) and web.config (Web based) file for C# applications

Once you create a web based application project, by default Web.Config file will be appear under solution Explorer.

If you create Windows based or Console application, there won’t be any config file under solution explorer, so we need to create manually.

Always try to use the Visual Studio IntelliSense to add variables, because there can be many possibilities to make mistakes such as spelling mistakes and wrong key words.

First add the key word:

Then add user values into the relevant places.

In the application, first need to add the System.Configuration object reference

For windows form and console application:

For web based application:

Cheers!

SQL and T-SQL string and date time functions (FORMAT, STUFF, DATALENGTH, PATINDEX, SWITCHOFFSET)

String Functions

FORMAT: you can format an input value based on a format string, and optionally specify the culture as a third input where relevant. You can use any format string supported by the .NET Framework.

STUFF: The STUFF function operates on an input string provided as the first argument; then, from the character position indicated as the second argument, deletes the number of characters indicated by the third argument. Then it inserts in that position the string specified as the fourth argument.

LEFT / RIGHT : The LEFT and RIGHT functions extract a requested number of characters from the left and right ends of the input string, respectively. 

DATALENGTH : The DATALENGTH function returns the length of the input in terms of number of bytes.

String Concatenation: Handling null value.

PATINDEX : T-SQL also supports a function called PATINDEX that, like CHARINDEX, you can use to locate the first position of a string within another string. But whereas with CHARINDEX you’re looking for a constant string, with PATINDEX you’re looking for a pattern.

Other String functions:

Date and Time Functions

GETDATE and CURRENT_TIMESTAMP both same GETDATE is T-SQL function and CURRENT_TIMESTAMP is standard SQL.

SYSDATETIME and SYSDATETIMEOFFSET are similar, only returning the values as the more precise DATETIME2 and DATETIMEOFFSET types (including offset), respectively.

There are no built-in functions to return the current date or the current time: CAST(SYSDATETIME() AS DATE).

GETUTCDATE function returns the current date and time in UTC terms as a DATETIME type, and SYSUTCDATE does the same, only returning the result as the more precise DATETIME2 type.

With the SWITCHOFFSET function, you can return an input DATETIMEOFFSET value in a requested offset term. Consider the expression SWITCHOFFSET(SYSDATETIMEOFFSET(), '-08:00'). Regardless of the offset of the instance you are connected to, you request to present the current date and time value in terms of offset '-08:00'. If the system’s offset is, say, '-05:00', the function will compensate for this by subtracting three hours from the input value.

TODATETIMEOFFSET : You use it to construct a DATETIMEOFFSET value from two inputs: the first is a date and time value that is not offset aware, and the second is the offset. You can use this function when migrating from data that is not offset-aware, where you keep the local date and time value in one attribute, and the offset in another, to offset-aware data.

Other data time functions:

Cheers!