Powershell - Programmers Dictionary App

Powershell Capabilites

Powershell offers some direct ways to access file and folder properties, such as file size and file times, and to use these to help the developer. The powershell suite also offers simple ways to process file contents, text files being the most obvious and easiest thing a developer may want to process. The toolset we want for our projects needed to offer things like Quick Search and Replace, Recursive Directory Search with Sorted by Last Update, and File and Folder Comparison. The particular capability I wanted to look at in this blog post is a Word/Phrase Indexing and Counting script.

Scenario

I am trying to simplify some updates to JavaScript modules for another project, and part of this involves seeing which JavaScript functions are used most often throughout a wider .Net web application. The .js functions are called from a range of pages and contexts, including external .js modules, flat file .html, dynamic .aspx pages and templates and so on. I needed the ability to count unique phrases in a single SOURCE file, to sort those phrases by length (longest down to shortest). The script then had a second cycle where it would count the frequency of each phrase across a whole solution, reading each TARGET file within a folder structure, recursing through the folder heirachy as needed. The desired output would be a list of unique phrases (or words) ranked by frequency/occurence from most popular downwards.

The original use of the tool was simply to identify JavaScript functions that are never called, or very rarely used, so as to remove them from the sourcecode or to weigh up if they are needed or not. A short .js function that is called only once could possibily be written inline instead of thrown into a module or namespace, either attached to an On Click event or executed on Document Load etc.

When I started looking at what this script would need to do exactly, the stages in the process to get the results expected, a few other use cases and permutations sprang to mind. These include -

  • Coders word count
  • Identifying excessive repetition of simple code
  • Identifying excessivlely long function names
  • Identifying meaningless variable names
  • Preventing variable name collisions
  • Coherent function and variable naming conventions
  • Trimming unused code
  • Unused variable declarations
  • Code obfuscation

The general intention was to have a Console app written in .VB or .C# which would pull on the PowerShell needed to accomplish these routine jobs. The Console app would have to be versatile in knowing what constitutes a unique word.

In the case of JavaScript function names like $NS.Context.Function might be the target for the Dictionary, in which case Symbols like $ and . would be part of the phrases listed and Character Case may or may not need to be observed.

In the case of blog articles the Dictionary might be aligned along the lines of normal English language in which case words/phrases would be separated by Space characters along with basic punctuation like full stop, comma, colon.

The app had to include flexibility to alternate from one scenario to another. It would also need to accept parameters to know what kind of output the developer was looking for. The initial requirement was that the app would simply produce a delimeted text file listing the unique words themselves. The second stage would be to have those sorted by length, sorted alphabetically, or sorted by grouping.

The PowerShell capabilities

The modules we needed in PowerShell are the Measure Object which can be performed on files and folders alike. My original findings on Measure Object began at Microsoft TechNet where there is a handy tutorial at doing some PowerShell scripting from the command line. This got things off to a good start as the scripts could easily Recurse folders and produce formatted output. Measure Object is found within the Get-ChildItem module

Get-ChildItem Measure-Object

examples::

files in folder

Get-ChildItem -Recurse -force | Measure-Object

lines in file

Get-Content C:\fso\a.txt | Measure-Object –Line

characters in file

Get-Content C:\fso\a.txt | Measure-Object –Character

words in file

Get-Content C:\fso\a.txt | Measure-Object –Word

using for directory stats (files, length, average size)

Get-ChildItem -Filter *.txt | Measure-Object -Property length -Maximum -Minimum -Average -Sum

pipe output into tabular format

Get-ChildItem -Filter *.txt | Measure-Object -Property length -Maximum -Minimum -Average -Sum | ft count, @{“Label”=”Average size(KB)”;”Expression”={($_.average/1KB).tostring(0)}}


600 days later - experiences with MS Bizspark

600 Days into starting a business

Having been within a startup for sometime I can tell you a bit more about what Bizspark has done for this cash strapped start-up. Initial expecations were cautious in that an offer of saving money with expensive software and computer services can seem almost to-good-to-be-true. Further down the road I can more confidently say that the promise made by the Bizspark offering has turned out to be reliable and certainly a welcome support. Some worries about being turned down, or removed from the scheme earlier than the 3 year grace period, or worries about being audited, scrutinized or refused benefits have all turned out to be unneccessary fears which anyone with a cash sensitive business is almost certain to encounter.

The offer made by the scheme has turned out to be genuine and so far, roughly half-way in, has come without downsides or strings attached. The savings in terms of access to software are hard to quantify - free use of latest Microsoft apps is a good opportunity to see the products themselves in a pressure free sandbox, without concerns of choosing which feature set, or skipping version upgrades, or looking for that free open-source alternative. Having opted for MS on a large amount of the systems used here it goes without saying some software choices are strongly in the favour of the Operating System provider. Worries about lack of code transparency, lack of competition, and market domination aside, a free hand at checking out the major application and server suites has been valuable in a lot of ways.

One advantage has been the removal of the limitations the trial and ‘lite’ product variants come with; Microsoft trials and downloads invariably come with a standard 180 day use limit after which the product de-activates or requires licensing and payment. Using these packages in a demanding environment is always a concern as quite often the 180 day period expires at the same time that you become dependent or confident with using the product. Being locked out of an important bit of software whilst working to a demanding schedule is a recipe for disaster, still worse would be failing to meet an important deadline because some software has decided to shutdown and potentially taken away your access to valuable business data or files.

The same goes for products like Sharepoint, Exchange Server, Windows Server family and SQL Database. The free variants and the older unsupported versions are well known, and the limitations that go with them are soon a sore point in getting work done. Trying out free trials is nice inside an academic or hobbyist kind of setting. In a big business, the pressures arent quite the same and it is easier to ask a corporate employer or a large IT team to write a cheque for the latest version of something once you can prove that it would be indespensible, in the right hands of course.

The same trial stuff in a budget concious new firm sounds alarm bells from the get-go. Server software is often a core requirement for many new businesses, not simply technology outfits but all kinds of modern company will have a requirement for solid, stable, secure server capacity and will not want to loose time, energy and capital in correcting problems - be it the wrong choice of server product, software incompatibility, lack of appropriate hardware or lack of training within the workforce. Any new firm needs to get the choices right at an early stage - any linux advocate or open source champion will be scratching heads by now without a doubt - but there are many people who would happily take the same chance as we have, even people with intense workloads and very tight spending limits.

Main worries here are around what choices to make coming out of Bizspark. Certainly choices need to be made and they are bound to include paying for some of the services and products. Am curious to know how other scheme members feel once inside that 3rd and final year of benefits. Do they often find themselves getting away from the product tie in and lock in scenario? Do they find themselves scaling back on feature sets to pay for and deciding to pay for the low powered, starter edition of MS Server or even going back to free versions, outdated licensed versions? Or even worse are people finding themselves in the position of back-tracking on the choice of provider and switching out from Microsoft solutions to either free or linux equivalents?

This end of things I would very much value some feedback and response about… Can you add anything to our journey here by your account with being in the Bizspark scheme?? More than anything, how would you sum things up - has the scheme turned out to be wholly beneficial, or more trouble than you expected? Do you have mixed feelings about the whole idea? Is Bizspark something you would point out to the next outfit who are in similar circumstances that you found yourself? Would you avoid any commercial tie in, avoid any restriction of environment like the absolute plague? Or maybe you would shout about how much cost and headache the benefits gave you, just at the time you might have needed real support, in a real essential part of taking a new business from drawing board and out to the world and eventually to market? Did Microsoft help your business survive? Let the readers know if you can, comments are open at the foot of the post!

Thanks for coming back for the update…

For more stories, look under the bizspark tag.


KB2804678 Cannot Exceed 256 Dynamic MAC Addresses By Default On Hyper-V Host

A niche scenario for those of you virtualising in hyper-v with more than 256 virtual network adapters on the host machine.

This is not a scenario I’ve encountered … not too many of us will ever have more than 256 virtual NICs _on _a single host. Microsoft has posted a support article on this scenario:

Windows Hyper-V server has a default limit of 256 dynamic MAC addresses. You have a Windows Server 2012 (WS2012) host which is configured with the Hyper-V role. The Hyper-V server is configured to provide dynamic MAC addresses to the guest machines:

When you start a guest virtual machine, you may encounter the following error message:

The application encountered an error while attempting to change the state of ‘’

Synthetic Ethernet Port (Instance ID CCE417C5-BDD9-4216-85CA-248620EE75C6): Failed to power on with Error ‘Attempt to access invalid address’.

On a Windows Server 2008 or Windows Server 2008 R2 Hyper-V host, an Event ID 12565 from source “Microsoft-Windows-Hyper-V-Worker” is logged.

Here’s how this issue is caused. Each host has a default dynamic pool of MAC addresses. This pool is generated as follows:

  1. The first three octets of the MAC address pool are 00:15:5D. This is unique to all Microsoft “devices”, complying with IEEE standards.
  2. The next two octets (4 and 5) in the MAC address pool are derived from the IP address of the host (the last 2 octets of the IP address to be precise). This gives the pool some uniqueness in your server farm. We now have 5 of the 6 required octets for a MAC address.
  3. Finally, the last octet in the MAC address pool is the range 0×0-0xFF. Each instance of this range is used once per virtual NIC (assuming that it’s using a dynamic MAC) on this host

Octets 1 to 3 are unique to Microsoft “devices”. Octets 4 and 5 are for giving the MAC address pool uniqueness for the pool range. And octet number 6 is used to make each dynamic MAC address unique on the network.

If you want to break out a scientific calculator or if you know your computer science, the clue to the cause is in that last piece of the puzzle. We only have octet 6 for each dynamic MAC address instance that can be allocated. An octet is 8 bits, from 00000000 to 11111111. That is 00 to FF in hex. Which is 0 to 255, or 256 numbers. And that means each host can have 256 MAC addresses, by default.

There are workarounds to this, if you are in the very rare situation where you need more than 256 MAC addresses on a single host.

Use Static MAC Addresses

Turn off (shut down) the VM and assign static MAC addresses. System Center VMM makes this easy with centrally human managed pool of MAC addresses, something like with DHCP.

Manually Modify The Per-Host Dynamic MAC Address Pool

Be very careful with this! You don’t want to create overlapping pools of MAC addresses to confuse ARP on your network. The MAC address range is defined by two registry values in HKLMSoftwareMicrosoftWindows NTCurrentVersionVirtualization:

  • MinimumMacAddress
  • MaximumMacAddress

kb2804678

In my example (above), you can see the range runs from 00:15:5d:01:86:ff. 00:15:5d is the manufacturer unique 3 octets for Microsoft “devices”. 01:86 is unique to the range on this host. And 00 to ff is the default range that limits us to 256 MAC addresses.

An interesting thought that came to me after posting this: you might want to be careful if using DHCP for your hosts – seriously, you should not do this! I can imagine how two hosts could end up accidentally with the same default range if HostA has Address1 when it is initialised, and then Address1 is allocated to HostB when it is initialised.

Microsoft says that you can modify this range … but as I warned before: be careful not to overlap over ranges or devices on your network! You can do this by modifying the fifth and or _sixth _octet of the default calculated dynamic MAC address range. Changing the _fifth _octet is the risky one … remember that the 4th and 5th octets are unique per host.

If you don’t have this massive environment then don’t touch these dynamic MAC address ranges unless you have to. If you do have to, then (in my opinion) you should be using System Center. A SQL whiz might be able to run a query in ConfigMgr to report on existing MAC addresses from physical devices. PowerShell will come in handy if you want to get the details exiting MAC ranges on your Hyper-V hosts. Stack Overflow has a script example to query lots of servers that you could tweak. 4sysops has another script example.


KB2744129 – Cannot Run Windows 8 Or Windows Server 2012 VM In Windows Server 2008 R2 Hyper-V

Microsoft released an update to deal with an issue where you cannot run a Win8-based or WS2012-based virtual machine on W2008 R2 Hyper-V.

_Assume that you have the Hyper-V server role installed on a computer that is running Windows Server 2008 R2. You create a virtual machine that is running Windows 8 or Windows Server 2012 on the computer. However, you experience one or more of the following issues: _

  • _The virtual machine stops responding. _
  • You receive a Stop error message, and the computer restarts. This behavior stops all running virtual machines together with the computer.

The issue occurs because the Hypervisor does not handle the one-shot synthetic timer correctly. Note The one-shot synthetic timer is also known as the aperiodic timer.

There is a publicly available update that you can download to resolve this issue.


How to choose a Blogging platform

Thinking of starting a Blog?

Read on to see our pick of the best blogging software, compared side by side for your appraisal. We outline the important pitfalls of each as well as the most compelling features which set them apart.

Wordpress

Most casual users will be more familiar with wordpress than any other platform, simply from browsing the web and reading Wordpress hosted content. It has become the go-to solution for business owners wishing control of their own content and the room to grow and customise at will. Wordpress offer a paid hosted service as well as the option to download and run the platform on your own Linux or Windows servers. A bog standard Wordpress installation leaves much to be desired visually but thanks to the large user base it is more easily extended and customised than any other platform. If you want to retain control of your blog, have large volumes of content, or want the ability to easily move between hosting providers, Wordpress is a good choice.

Blogger

Blogger is owned and maintained by Google which is important consideration for many. They are a major player in hosted blogs, and well known for having zero start up costs and for those eager to start writing immeadiately. Blogger is used by many hobbyists, amateurs and for personal sites. Most business startups would usually opt for Typepad or Wordpress.

Movable Type

Movable Type has built a solid user base as a competitor to the likes of Wordpress. They offer a professional hosted service for businesses as well as an entry level product for personal use. It is heavily extendable as a platform and has many high profile users including US President Obama.

Typepad

Typepad is a hosted service, which has an entry level free product alongside a paid, professional hosted product. They have a free trial which may be of interest to businesses who intend to get started quickly and have a large volume of posts to serve. If you are thinking about hosting blogs for each team member, or if search engine rankings are a top priority, Typepad should be a serious choice.

Tumblr

Tumblr is more akin to micro-blogging sites such as Twitter. For those wanting a simple solution to make timely, but short updates, Tumblr is a very attractive option. For those wishing to heavily customize the look and feel of the blog, Tumblr may have too much of a learning curve. More suited to creative types, a well thought out Tumblr site can be very visual and compelling but beware of the effort required to get that look just right.

Squarespace

Squarespace is a professional paid product. Blogs created on squarespace are visually appealing and can be themed and styled to work nicely alongside a corporate website. It is also reliable and because it is a hosted service, very easy to setup. Those businesses who are bootstrapped may want to consider the pricing and cost of designers before making a final selection.

Posterous

Posterous is ideal for those businesses who don’t want to dabble too much in programming code or hiring developers. It is seriously simple to set up and is a proven and reliable product. For the small business, it is well worth being on your shortlist.

Livejournal

LiveJournal was one of the earliest platforms and is supported by a loyal base of users. Recently, it has been overtaken by the likes of Wordpress and has failed to catch up in terms of rich features. It does have its own integrated social platform but is more suited to hobbyists than new businesses.


Joining the Bizspark Programme

Bizspark

As a new business we recently qualified for Microsoft Bizspark membership. Bizspark is provided by Microsoft to help new businesses, especially software & web startups, to get off the ground. Bizspark provides a range of benefits like

  • 3 years of free Microsoft licensing (think Windows Server, SQL Server, Dynamics, Exchange, System Centre)
  • Free MSDN membership (Visual Studio)
  • Office 2013 and Office 365 licenses
  • Access to MS product training
  • Free credits to use on cloud computing, specifically Windows Azure

For the latest news, follow the bizspark tag to read more on our blog.


Why host with Jekyll

What’s the problem?

In the past, weve spent a lot of time and effort setting up and customising blogs, bulletins and various content management. These have all been powered with .net or PHP code and backed by cloud databases in SQL/MySQL. Once a package has been chosen and all the required plugins and modules have been put together, you have a site than can be grown over time by a number of contributors, where expansion and backup can be looked after easily and where themes and branding made consistent without headaches.

CMS Lock-in

My existing blog was running WordPress. Deep down, WordPress is a tangle of PHP spaghetti code. Even though WordPress is free to use, simple, well maintained you are still dependent on a lot of third-party programming from outside the WordPress developer team to get various functionality. Over time, the effect of WordPress throwing in new features has broken a lot of the customisation people had written to go along with it, and people found they were spending more time tweaking there blog servers than composing new content.

What does Jekyll do different?

The idea is that you define the components of your site (pages, blog posts, etc) in a simple text files, then the site generator processes those text files and writes out .html files to be consumed by visitors. This good thing about this is that no code needs to run on your web-server. For the first time, your IIS or Apache install can concentrate on serving pages rather than running server side C#,VB,PHP etc. The .html files are pre-processed as a batch, and this makes for a faster, more secure experience.


How to reverse proxy in IIS

Why use reverse proxying?

Without Application Request Routing

When you are hosting sites in IIS, sometimes you have the need to pass certain requests to other machines within your local network. For example, we are using IIS for web hosting and CMS but also have the need to serve pages from Linux on development machines. A common workaround is to use port-forwarding, in which case IIS can serve your regular pages on ports 80 and 443 and requests targeted for your development pages can be made on custom port numbers (generally above port 1000).

What if you wanted to use standard port numbers for all the requests, and you needed IIS to differentiate based on the host-name itself? This is where Reverse Proxying comes in with the help of IIS modules ARR and URL Rewrite.

When you are hosting sites in IIS, sometimes you have the need to pass certain requests to other machines within your local network. For example, we are using IIS for web hosting and CMS but also have the need to serve pages from Linux on development machines. A common workaround is to use port-forwarding, in which case IIS can serve your regular pages on ports 80 and 443 and requests targeted for your development pages can be made on custom port numbers (generally above port 1000). With some simple port forwarding rules set-up on the firewall a scenario like this would be possible

    www.example.com       --> served by IIS port 80 for host "www.example.com"
    mail.example.com:8081 --> served elsewhere in subnet on port 8081

What if you wanted to use standard port numbers for all the requests, and you needed IIS to differentiate based on the host-name itself? This is where Reverse Proxying comes in with the help of IIS modules ARR and URL Rewrite.

What is Reverse Proxy and how can it help?

With Application Request Routing

When you are hosting sites in IIS, sometimes you have the need to pass certain requests to other machines within your local network.

Take the example of a simple development setup using IIS for web hosting and CMS but also running Linux instances in virtual machines. The machine is setup to be visible to the web with proper port forwarding rules on the firewall. It also has its internal firewall open to the services it is offering.

So IIS is serving pages for www.example.com on standard ports 80 and 443. The IIS box is also running a Hyper-V machine with a popular LAMP setup. The Linux VM cannot listen on ports 80 and 443 because they are being used by IIS. The only way to get requests to the LAMP VM is by using non-standard ports, in this case 8080 and 8081

A common workaround is to use port-forwarding, in which case IIS can serve your regular pages on ports 80 and 443 and requests targeted for your development pages can be made on custom port numbers (generally above port 1000). With some simple port forwarding rules set-up on the firewall a scenario like this would be possibe

What if you wanted to use standard port numbers for all the requests, and you needed IIS to differentiate based on the host-name itself? This is where Reverse Proxying comes in with the help of IIS modules ARR and URL Rewrite.

    www.example.com       --> IIS port 80
    mail.example.com:8080 --> Apache port 8080

KB2908783 - Data Corruption Occurs On iSCSI LUNs In Windows

Another niche scenario bug is fixed in this update by Microsoft, affecting the following Windows versions/editions:

  • Windows 8 & Windows Server 2012
  • Windows 7 & Windows Server 2008 R2

Symptoms

Consider the following scenario:

You have a computer that is running Windows 8, Windows Server 2012, Windows 7 Service Pack 1 (SP1), or Windows Server 2008 R2 SP1. You create iSCSI connections to multiple iSCSI targets which are storage arrays. There are frequent iSCSI session connections and disconnections, such as logical unit number (LUN) arrivals and removals. In this scenario, a silent read/write data corruption can occur on an iSCSI LUN.

There is a bunch of links for downloading updates to resolve the issue, depending on your OS and architecture. See the original post by Microsoft for links.


OLAP cubes using SQL Server Analysis Services

Introduction

This article is created to help those technical newbies who want to learn creation of OLAP Cube in SSAS (SQL Server Analysis Services) using Microsoft BIDS (Business Intelligence Development Studio) Environment and Data Warehouse. We will first take a glimpse at the basic introduction to requirement of OLAP Cube, and then create OLAP Cube in SQL Server Analysis Service by following 10 easy steps.

Before preparing OLAP Cube, we need to create and populate our data warehouse. If you are totally new to Data Warehouse concepts, please take a quick look at my previous article “Create First Data Warehouse” which can give you a brief idea on data warehouse concepts which are used in this article.

Brief Introduction to OLAP Cube

What is OLAP Cube & Why do we need it?

  • An OLAP cube is a technology that stores data in an optimized way to provide a quick response to various types of complex queries by using dimensions and measures.
    Most cubes store pre-aggregates of the measures with its special storage structure to provide quick response to queries.

  • SSRS Reports and Excel Power Pivot is used as front end for Reporting and data analysis with SSAS (SQL Server Analysis Services) OLAP Cube.
  • SSAS (SQL Server Analysis Services) is Microsoft BI Tool for creating Online Analytical Processing and data mining functionality.
  • BIDS (Business Intelligence Development Studio) provides environment for developing your OLAP Cube and Deploy on SQL Server.
  • BIDS (Business Intelligence Development Studio) comes with Microsoft SQL Server 2005, 2008 (e.g. Developer, Enterprise Edition) .
  • We have to choose OLAP Cube when performance is a key factor, the key decision makers of the company can ask for statistics from the data anytime from your huge database.
  • We can perform various types of analysis on data stored in Cube, it is also possible to create data mining structure on this data which can be helpful in forecasting, prediction.

What is the difference between OLAP and OLTP?

Online Transaction Processing (OLTP) Online Analytical Processing (OLAP)
Designed to support Daily DML Operations of your applicatio Designed to hold historical data for analyses and forecast business needs
Holds daily Latest Transactional Data related to your application Data is consistent up to the last update that occurred in your Cube
Data stored in normalized format Data stored in denormalized format
Databases size is usually around 100 MB to 100 GB Databases size is usually around 100 GB to a few TB
Used by normal users Used by users who are associated with the decision making process, e.g., Managers, CEO.
CPU, RAM, HDD space requirement is less. CPU, RAM, HDD space requirement is higher.
Query response may be slower if the amount of data is very large, it can impact the reporting performance. Query Response is quicker, management can do Trend analysis on their data easily and generate quicker reports.
T-SQL language used for query MDX is used for querying on OLAP Cube

Basic Architecture

In our case, data warehouse is used as a source of data to Cube in BIDS. Once Cube gets ready with data, users can run queries on Cube created in SSAS. SSRS Reports and Excel Pivoting/Power Pivot can use OLAP Cube as source of data instead of OLTP database to get performance for resolving Complex Queries.

SSRS Reports, Excel Power Pivot can be used for visualization/analysis of data from cube.

Scenario

X-Mart is having different malls in our city, where daily sales take place for various products. Higher management is facing an issue while decision making due to non availability of integrated data they can’t do study on their data as per their requirement. So they asked us to design a system which can help them quickly in decision making and provide Return on Investment (ROI).

So as a part of the design, we had completed designing of First Data Warehouse in my previous article. Now we have to Design / Create OLAP Cube in SSAS, on which our reports can do a quick query and we can also provide self service BI capability to users later on.

Creating Data Warehouse

Let us execute our T-SQL Script to create data warehouse with fact tables, dimensions and populate them with appropriate test values.

Download T-SQL script attached with this article for creation of Sales Data Warehouse or download from this article “Create First Data Warehouse” and run it in your SQL Server.

Follow the given steps to run the query in SSMS (SQL Server Management Studio).

  1. Open SQL Server Management Studio 2008
  2. Connect Database Engine
  3. Open New Query editor
  4. Copy paste Scripts given below in various steps in new query editor window one by one
  5. To run the given SQL Script, press F5
  6. It will create and populate “Sales_DW” database on your SQL Server

Developing an OLAP Cube

For creation of OLAP Cube in Microsoft BIDS Environment, follow the 10 easy steps given below.

Step 1: Start BIDS Environment

Click on Start Menu -> Microsoft SQL Server 2008 R2 -> Click SQL Server Business Intelligence Development Studio.

Step 2: Start Analysis Services Project

Click File -> New -> Project ->Business Intelligence Projects ->select Analysis Services Project-> Assign Project Name -> Click OK

Step 3: Creating New Data Source

3.1 In Solution Explorer, Right click on Data Source -> Click New Data Source

3.2 Click on Next

3.3 Click on New Button

3.4 Creating **New connection **

  1. Specify Your SQL Server Name where your Data Warehouse was created
  2. Select Radio Button according to your SQL Server Authentication mode
  3. Specify your Credentials using which you can connect to your SQL Server
  4. Select database Sales_DW.
  5. Click on Test Connection and verify for its success
  6. Click OK.

3.5 Select Connection created in Data Connections-> Click Next

3.6 Select Option Inherit

3.7 Assign Data Source Name -> Click Finish

Step 4: Creating New Data Source View

4.1 In the Solution Explorer, Right Click on Data Source View -> Click on New Data Source View

4.2 Click Next

4.3 Select** Relational Data Source **we have created previously (Sales_DW)-> Click **Next **

4.4 First move your Fact Table to the right side to include in object list.

Select** **FactProductSales Table -> Click on Arrow Button to move the selected object to Right Pane.

4.5 Now to add dimensions which are related to your Fact Table, follow the given steps:

Select Fact Table in Right Pane (Fact product Sales) -> Click On** Add Related Tables **

4.6 It will add all associated dimensions to your Fact table as per relationship specified in your SQL DW (Sales_DW).

Click Next.

4.7 Assign** Name (SalesDW DSV)-> Click Finish **

4.8 **Now Data Source View is ready to use. **

Step 5: Creating New Cube

5.1 In Solution Explorer -> Right Click on Cube-> **Click New Cube**

5.2 Click** Next **

5.3 Select Option** Use existing Tables -> Click Next **

5.4 Select Fact Table Name from** Measure Group Tables (FactProductSales) -> Click Next **

5.5 Choose** Measures from the List which you want to place in your Cube –> Click **Next

5.6 Select All Dimensions here which are associated with your Fact Table-> Click Next

5.7 Assign Cube Name (SalesAnalyticalCube) -> Click Finish

5.8 Now your Cube is ready, you can see the newly created cube and dimensions added in your solution explorer.

Step 6: Dimension Modification

In Solution Explorer, double click on dimension **Dim Product -> **Drag and Drop Product Name from Table in Data Source View and Add in Attribute Pane at left side.

Step 7: Creating Attribute Hierarchy In Date Dimension

Double click On Dim Date dimension -> Drag and Drop Fields from Table shown in Data Source View to Attributes-> Drag and Drop attributes from leftmost pane of attributes to middle pane of Hierarchy.

Drag fields in sequence from Attributes to Hierarchy window (Year, Quarter Name, Month Name, Week of the Month, Full Date UK),

Step 8: Deploy the Cube

8.1 In Solution Explorer, right click on Project Name (SalesDataAnalysis) –

Click Properties

8.2 Set Deployment Properties First

In Configuration Properties, Select Deployment-> Assign Your SQL Server Instance Name Where Analysis Services Is Installed (mubin-pc\fairy) (Machine Name\Instance Name) -> Choose Deployment Mode Deploy All **as of now ->Select Processing Option **Do Not Process **-> Click **OK

8.3 In Solution Explorer, right click on Project Name (SalesDataAnalysis) – > Click Deploy

8.4 Once Deployment will finish, you can see the message** Deployment Completed **in deployment Properties.

Step 9: Process the Cube

9.1 In Solution Explorer, right click on Project Name (SalesDataAnalysis) –

Click Process

9.2 Click on Run button to process the Cube

9.3 Once processing is complete, you can see Status as **Process Succeeded **–>Click **Close **to close both the open windows for processing one after the other.

Step 10: Browse the Cube for Analysis

10.1 In Solution Explorer, right click on Cube Name (SalesDataAnalysisCube) – > Click Browse

10.2 Drag and drop measures in to Detail fields, & Drag and Drop Dimension Attributes in Row Field or Column fields.

Now to Browse Our Cube

  1. Product Name Drag & Drop into Column
  2. Full Date UK Drag & Drop into Row Field
  3. FactProductSalesCount Drop this measure in Detail area

We will see some more features shortly - how can we assign user friendly names, named calculations, named query, ordering on hierarchy, hiding dimension attributes, creating perspective, KPI, security roles, etc.

Friends, if you liked my article, please do not forget to vote for me.

Enjoy SQL Intelligence.