Date Dimension Generator

Following SQL query is to create simple date dimension table and then insert values to the given range.

Create table

CREATE TABLE [dbo].[DimDate](
[Date] [date] NOT NULL,
[Day] [varchar](10) NULL,
[Month] [varchar](10) NULL,
[Year] [int] NULL
) ON [PRIMARY]
GO

Script to generate date


  DECLARE @startDate date;
  DECLARE @endDate date;

  SET @startDate = '2017-01-01';
  SET @endDate = '2018-12-31';

 
  WHILE(@startDate <= @endDate)
            BEGIN
                        INSERT dbo.DimDate
                        SELECT
CONVERT(Datetime, @startDate, 120) [Date],
DATENAME(DW,@startDate) [Day],
DATENAME(MM,@startDate) [Month],
DATEPART(YY,@startDate) [Year]

                        SET @startDate = DATEADD(DD, 1, @startDate )
            END

Dimensional modeling

After working with .NET and SharePoint technologies for long time, I started to look in BI Analytics area. This article is to give an overview of data warehouse, dimensional modeling, and fact constellation. First of all we will see what is data warehouse and data mart.

Data warehouse and data mart

Data mart is a subset of data ware house where it represents a specific business scope. Multiple data marts together create a data warehouse. In a typical organization, there are multiple departments focusing different business objectives such as IT, administration, finance, manufacturing, HR, etc. each department data can be considered as data marts and altogether it will come under a data ware house.

Data marts often holds only one subject are as explained in the above figure. Also it may holds more summarized data. Data mart focused on a dimensional model using star schema. Whereas data warehouse holds multiple subject areas in very detailed manner. It works to integrate all data sources. Data warehouse does not necessarily use a dimensional model but feeds dimensional models.

Start schema and snow flake

Start schema and snowflake are two different ways to organize data marts and data warehouse using relational database. Both methods use dimension tables to describe aggregate data in fact tables.

Star schema has single fact table connected to multiple dimension tables and it visualize as star. In this model only one link establishes the relationship between the fact table and any of the dimension tables. Following figure explains the design of the star schema.

Snowflake schema can be seen as the extension of star schema. Here large dimension tables are normalized into multiple sub dimension tables. Every dimension table in this model is associated with sub dimension table and multiple links. Following figure explains the design of the snowflake schema.

The query cannot be run for the following DataObject: Get User Profile By Name: SharePoint 2013

I was developing a heavy InfoPath application with custom codes. The form contains around 40 data connection including external data connections, web services, etc… This form is using ‘Get User Profile By Name’ data connection which come along with the UserProfileService.asmx web services. The user profile web service url comes as follows: http://shpapp:2000/sites/Test/_vti_bin/UserProfileService.asmx

When I try to open the form in the preview mode it pops up below error message:

Error message:

The query cannot be run for the following DataObject: Get User Profile By Name

InfoPath cannot run the specified query.

The SOAP response indicates that an error occurred: A user with the account name MohamedAkb could not be found. ---> An error was encountered while retrieving the user profile.

UserCannotBeFoundAn error was encountered while retrieving the user profile.

I started search, almost all the suggestions say set the form in to full trust mode as bellow:

• Go to File - > Form Options -> Security and Trust
• Select Full Trust option button.

The above option is use full to deploy the form at the farm level. If the form contains VSTA code, it is must to deploy as full trust mode. This doesn’t help to resolve the user profile issue.

To resolve the user profile issue follow below steps:

• At the create data connection wizard untick the ‘Automatically retrieve data when form is opened’ option.

• At the form load event create a rule to query the data connection. In the rule, set the query field as Account name = DOMAIN\UserName then query the data connection.

• Save the form and open in the preview mode. You will see no errors now. 

Change URL of a Document Library - SharePoint 2013

After creating the document library in SharePoint 2013, sometimes you might need to change the URL of the library. You can follow below steps in the browser:

            • Open the document library

            • Go to library settings from Library tab at the ribbon

            • Click 'List, name, description and navigation' link.

Using above steps you can change only the library name, but the URL will not change.

Resolution

Using the browser there is no way to change the URL. You can do this using SharePoint Designer 2013. The steps as follows:

            • Open the SP designer and click on the 'All Files' not the 'List and Libraries'

            • Right click on the library and select 'Rename'

This will change the library name as well as the URL.

The Internet is Not Really Free

Lot of resources on the internet seems free. We are using it free of charge. There should be a way to the content providers to earn money. This is where the digital footprint information is used for the business purposes. Though the online services seems free, those are not actually free of charge.

Tracking the user information, customizing the content and using the analyzed data becoming a growing business in internet. There are specialized people in this area, they observe and collect the data and link with other sources. Mainly marketers and advertiser use this technique to promote their product.

There is no direct cost involve on the internet to watch a video, read a blog, view a web site, etc. Though the users are not paying directly, they are paying indirectly. As described above the content providers need money to maintain their services, to their servers, their database storages and network facilities. The content providers earn money indirectly to run their services.

The question is how the content providers can earn money indirectly from the users. The service is funded by monetizing information about the internet user, about his preferences, his interests and his social circle, basically the information collected in the form of digital footprint. If the user pay a subscription or not, these information will be collected and used for various purposes.

The users may notice that when they visiting a web page there are so many advertisement all over the pages. Most of the advertisements are matching with the users’ preferences. How the content provider predict the correct advertisement is from the collected digital footprint information. For example a user searching for a shirt in an online store and he is interested on it. This data will be stored, next time the user log in to a web page he will notice the shirt advertisements are popping up at the page.

To select the right advertisement on the page, the marketers and the advertisers may choose based on the demography, language, right product, the correct time and other factors; that will help to target the consumers effectively.

Some intermediate parties playing a role between the user and the advertisement companies. The intermediate parties collect user information such as user profile, preferences, language and single sign-on data, and they analyze and process the information and provide to advertisement companies. There may be no direct connection between the user and the advertisement parties, the connection resides through intermediate parties.

Add list of users to SharePoint group using powershell

After migrating SharePoint 2010 site to SharePoint 2013 the user permission is not working as in 2010. The users are getting access denied message. This is due to SharePoint 2013 use the claim based authentication mechanism. As a quick fix I wrote a power shell script to add users to a SharePoint group.

Store the users in a text file as bellow format:

i:0#.w|domain\username

Use the bellow script to add permission:

 $loc = Get-Location;  
 $Users = Get-Content "$loc\Users\Users.txt";      
 foreach ($User in $Users) {       
 write-host($User);  
 Set-SPUser -Identity $User -Web "http://shpapp:2000/sites/TaskApproval/" -Group " Task Confirmation Users";       
 }  

Site collection feature not visible when upgrade solution from SP 2010 to SP 2013

I migrated a SharePoint 2010 site to SharePoint 2013 farm. And crated a custom web part and deployed to the 2013 environment as usual PowerShell commands.

The commands as follows:

• Add-SPSolution -LiteralPath c:\TaskApp.wsp
• Install-SPSolution -Identity TaskApp.wsp -WebApplication http://shpapp-02/sites/TaskSite -GACDeployment

After deploying the wsp to a web application the feature was not visible in the site collection features page.

The issue is due to the migrated site is comes with the 2010 user experience. Once the wsp is deployed using the above command the feature goes to 15 hive which cannot be display in a 2010 user experience site.

Resolution

I have tried below 2 options both worked: 

1. Change the user experience to SP 2013 UI by following 'Start now' link at the top of the site.

After changing the user experience you can see the feature in the site collection features page.

If the user does not like to change the UI layout this option is not going to work out.

2. You can follow bellow commands to deploy the wsp:

• Install-SPSolution -Identity TaskApp.wsp -WebApplication http://shpapp-02/sites/TaskSite -CompatibilityLevel 14 -GACDeployment

This command is adding the feature to the 14 hive features directory, this will be displaying in the site collection features page.

http://shpapp02/sites/TaskSite/_layouts/ManageFeatures.aspx?Scope=Site