Skip to main content

Email Alerts are not received...

In one of application, Email alerts are sent using SQL sp_send_dbmail stored procedure. Email alerts were working properly, but suddenly noticed email alerts are not received.

To troubleshoot the issue, first I checked the msdb.dbo.sysmail_event_log table using the below query.

SELECT log_id, event_type, log_date, description, process_id, mailitem_id, account_id, last_mod_date, last_mod_user
FROM
msdb.dbo.sysmail_event_log 
ORDER BY
log_date DESC

If there is any error, you should see records with the event_type value as error.
In my case there are records with event_type value as error, and the description value for those records is same as below

The mail could not be sent to the recipients because of the mail server failure. (Sending Mail using Account 3 (2018-02-01T10:41:27). Exception Message: Cannot send mails to mail server. (The SMTP server requires a secure connection or the client was not authenticated. The server response was: Authentication required).  )

As per the error descript…

How to Combine Two Fact Tables in SSAS...

In SQL Server Analysis Services Cube, there may be a situation where we need to combine two related fact tables.
This kind of a situation may occur due to the same fact having different dimensions. For an example if we consider two dimensions as Branch and Region, in some areas Sales may happen just only in Regions and sometimes it may go to the level of Branch.

In such a scenario in the Data warehouse we need to have two different tables for those two type of sales like dbo.FactSalesRegion and dbo.FactSalesBranch.
But for analysis purposes we should have all the sales as one. Due to that we need to create one Fact by combining those two fact tables in the data warehouse.

We can do it in the Cube by creating a new fact table using Named Query.
Open the Data Source View by double clicking it in the Solution Explorer. Then right click on the design area and then select New Named Query.
Then provide a name for the combined fact table like FactSales and enter the SQL Query to get the union of dbo.FactSalesRegion and dbo.FactSalesBranch tables.

The query will looks like as below.

SELECT RegionKey, NULL AS BranchKey, Sales
FROM dbo.FactSalesRegion
UNION
SELECT NULL AS RegionKey, BranchKey, Sales
FROM dbo.FactSalesBranch

In the above query we add the non existing columns for each table as NULL and Union those tables, since those two tables which we get the union should have same columns.
The above query provides a new fact table as FactSales which has total sales.
In some complex situations this scenario helps to design the data warehouse.

There are few modifications to be done for the Cube to load data, otherwise it gives errors while processing due to those null values.
We need to modify some properties in those dimensions (in this example Region and Branch dimensions) and also need to change some settings in the dimension usage.

Double click on one of above dimension (assume DimRegion) in the Analysis Services Project. Then select the key (RegionKey) from Attributes and go to properties. In that expand the KeyColumns property and change the NullProcessing property to UnknownMember. After that you will see Key is underline by red color. To solve that issue, select DimRegion from Attributes and go to properties. In the bottom of that property list you can find a property as UnknownMember. Change the value of that property to Visible.

Then you should change some settings in the Dimension Usage area of the Cube. Double click on the Cube in the Solution Explorer and click on Dimension Usage tab. In that you can see the facts and the dimension used for those facts.
Select the RegionKey and click the button in the Right side to get the Define Relationship window. Click on Advanced button and you will get the Measure Group Bindings window.
Under Relationship change the Null Processing of the RegionKey to UnknownMember. Need to do this for all the facts which uses the dimension.

After that you can process the cube and load data.
Hopes this will help you as well...

Comments

  1. Hihi,Im a freshie in ssas.
    I having some problems in combining of two fact tables.
    May i ask a few questions.

    According to your post, you can combine the two fact tablees by union the related column.

    1. Im not really understand why we need to set Null for non-existing columns?

    2. Are we neccessary to union both of the fact tables? how about inner join?

    3. how can i write the new named query if the two fact tables has no related column or don't.

    4. Is it still got another way to combine two fact tables beside writing query. For example, using M2M dimension and intermediate measure group?

    ReplyDelete

Post a Comment