BI

How to Install Vertica in a single node.

Install Vertica in a single node. 

This article is mainly related to:

  1.  Setup of Vertica in a single node.
  2. Vertica Console Management
  3. Tableau
  4. DBeaver

During my free time, I want to try to install Vertica and want to know what it is all about. what are the things we should know while installing Vertica and what will be the issues, difficulties, requirements and process do we need to follow in order to setup Vertica and play around it.

The HP Vertica Analytic Database is based on a massively parallel processing (MPP), shared-nothing architecture, in which the query processing workload is divided among all nodes of the Vertica database.

If you want to try Vertica and play around along with this columnar database then you should follow below steps.

Step 1:

Firstly you should have any Linux OS installed in a machine. For Vertica, the minimum requirement is to have 3 nodes that mean’s three Linux OS running in different nodes. But, in my case I want to try to install in only one node and try it out. I have windows 7 OS install and where I have install Red hat Linux in my virtual machine. So, make sure you have at least one Linux OS installed machine.

You may find the below link to find out the minimum requirements and server configuration:http://my.vertica.com/docs/5.1.6/HTML/index.htm#18671.htm

General Platform Recommendations

  • ext4 is recommended over ext3 for performance reasons.
  • Use 2GB of swap space regardless of the amount of installed RAM.
  • Place the database /catalog directory on the same drive as the OS.

Step 2:

Download all the required software related to Vertica from the site https://my.vertica.com/downloads/

In order to download you can sign up in a community edition. All the stuffs you can know from below video:

http://www.vertica.com/files/myVerticaVideo/myVertica_Audio_Video_Combined_121009J.html

Here, I have downloaded the below versions:

Step 3:

After that move the setup files into Red Hat Linux virtual machine directory. I have put the setup files into directory as /data/software and then open the terminal in VM,

Run the below command as root user:

rpm -Uvh /data/Software/vertica-7.0.2-1.x86_64.RHEL5.rpm

Then after that it will ask to run the /opt/vertica/sbin/install_vertica to complete the installation.

Step 4:

Run the script in master node

# /opt/vertica/sbin/install_vertica -s host_list -r rpm_package -u dba_username

Here I used only one node so below is my script.

 /opt/vertica/sbin/install_vertica -s localhost -r /data/Software/vertica-7.0.2-1.x86_64.RHEL5.rpm -u dbadmin

Here, you need to note that if you want to install Vertica in multiple nodes then you can include different nodes or host list.

Where options are:

-s host_list comma-separated list of hostnames or IP addresses to include in the cluster; do not include space characters in the list.

  1. -r “vertica_6.0.x.x86_64.RHEL5.rpm”
  2. -u dbadmin user name
  3. -p dbadmin passowrd
  4. -P root password
  5. -L location of the license
  6. -d where data will be located
  7. -s nodes that will be part of the cluster
  8. -r location of the installation rpm

— If you omit the -u parameter, the default database administrator account name is dbadmin who will only use the admintools.

You can find more about installing vertica in 3nodes or complete cluster Installation in below link:

http://www.aodba.com/main_articles_single.php?art=83&page=vertica

Step5:

After that you will get some issues or errors as below which I have got

>> Validating node and cluster prerequisites…

Failures during local (OS) configuration for verify-127.0.0.1.xml:

HINT (S0305): https://my.vertica.com/docs/7.0.x/HTML/index.htm#cshid=S0305

TZ is unset for dbadmin. Consider updating .profile or .bashrc

HINT (S0041): https://my.vertica.com/docs/7.0.x/HTML/index.htm#cshid=S0041

Could not find the following tools normally provided by the mcelog

package: mcelog

HINT (S0040): https://my.vertica.com/docs/7.0.x/HTML/index.htm#cshid=S0040

Could not find the following tools normally provided by the pstack or

gstack package: pstack/gstack

WARN (N0010): https://my.vertica.com/docs/7.0.x/HTML/index.htm#cshid=N0010

Linux iptables (firewall) has some non-trivial rules in tables: filter

FAIL (S0150): https://my.vertica.com/docs/7.0.x/HTML/index.htm#cshid=S0150

These disks do not have ‘deadline’ or ‘noop’ IO scheduling: ‘/dev/sda1’

(‘sda’) = ‘cfq’, ‘/dev/sda3’ (‘sda’) = ‘cfq’

FAIL (S0020): https://my.vertica.com/docs/7.0.x/HTML/index.htm#cshid=S0020

Readahead size of sda (/dev/sda1,/dev/sda3) is too low for typical

systems: 256 < 2048

FAIL (S0030): https://my.vertica.com/docs/7.0.x/HTML/index.htm#cshid=S0030

ntpd process is not running: [‘ntpd’, ‘ntp’]

FAIL (S0081): https://my.vertica.com/docs/7.0.x/HTML/index.htm#cshid=S0081

SELinux appears to be enabled and not in permissive mode.

FAIL (S0310): https://my.vertica.com/docs/7.0.x/HTML/index.htm#cshid=S0310

Transparent hugepages is set to ‘always’. Must be ‘never’ or ‘madvise’.

Then go through each error in the link below

https://community.vertica.com/vertica/topics/

This is all about some cluster prerequisites. Here go through each FAIL(XXXX) in the community forum where you will get the solution for each error.

Step 6:

After that, run the admintools from the dbadmin user then you can see as

After that create the database:

Then choose the host name where database will reside:

Then, define the Catalog location and data path

Since I have installed Vertica in a single node so there will not be the concept of K-Safe method. If we are installing at least 3nodes then we can have k-safe.

After that,

Then create Vertica database as Vertica_DB in a single node.

After that the database will created.

 

Just click OK and you will see the Database configuration:

Then finally you can exit from the admintools:

Step 7:

Vertica management console:

After successful installation of Vertica Database now you can install the Vertica management console:

The new HP Vertica Management Console is an enterprise database management tool that provides a unified view of your HP Vertica database and lets you monitor multiple clusters from a single point of access.

You can find more on below link:

https://my.vertica.com/docs/5.1.6/HTML/index.htm#16773.htm

Run the command as a root user:

rpm –Uvh vertica-console-7.0.2-1.x86_64.RHEL5.rpm

After successful installation it will show URL as https://localhost.localdomain:5450/webui during the installation. Just go through the URL and accept the license

What you can do with Management Console

  • Create a database cluster on hosts that do not have HP Vertica installed
  • Create, import, and monitor multiple HP Vertica databases on one or more clusters from a single point of control
  • Create MC users and grant them access to MC and MC-managed databases
  • Manage user information and monitor their activity on MC
  • Configure database parameters and user settings dynamically
  • Access a single message box of alerts for all managed databases
  • Export all database messages or log/query details to a file
  • View license usage and conformance
  • Diagnose and resolve MC-related issues through a browser
  • Access a quick link to recent databases and clusters
  • View dynamic metrics about your database cluster

The features is so much helpful for the DBA’s and the developer from where they can easily monitor multiple HP Vertica databases on one or more clusters from a single point of control.

Detail setp to install and login setup process is shown in my slideshare , you can see the link below :

https://www.slideshare.net/anil_maharjan/how-to-setup-vertica-in-a-single-node

Tableau:

Tableau can help anyone see and understand their data. Connect to almost any database, drag and drop to create visualizations, and share with a click.

To know the story behind your data which is stored in Vertica DB, you need to have a reporting tool which can make a quick decision and helps you to get the value of your data.

This is where Tableau can be used. Which is one of the best Reporting Tool I must say.

One can go to Tableau portal and download the software and play around any database, Worksheets, excel files and so many other data files.

I have downloaded both desktop and public version of Tableau, and where as desktop version works for 15 days trail.

Now in order to connect Vertica DB through Tableau you need to install the Vertica client in your PC i.e. my windows 7 machine, where you need to download the vertica-client-7.0.2-1.64 from https://my.vertica.com/

Also, you can install the test db along with Vertica as VMART schema but in my case I am getting error so I have posted into Vertica community .One can join this community and can share ideas and issues.

https://community.vertica.com/vertica/topics/cannot-create-vmart-example-db

After that you can open tableau desktop and connect to Vertica DB in order to play around with and to find the story behind your data.

Also, you can have public tableau install in your PC and do some analysis using different worksheets, I have also done some analysis related to ‘Average Percentage of Internet Users across the World ‘ and published into public server which is free that Tableau providing for normal users.

https://public.tableausoftware.com/views/AveragePercentageofInternetUsersaccrostheWorld/AverageofIndividualsUsingtheInternet?:showVizHome=no#1

DBeaver:

DBeaver is free and open source (GPL) universal database tool for developers and database administrators.

  • Usability is the main goal of this project, program UI is carefully designed and implemented.
  • It is freeware.
  • It is multiplatform.
  • It is based on opensource framework and allows writing of various extensions (plugins).
  • It supports any database having a JDBC driver.
  • It may handle any external datasource which may or may not have a JDBC driver.
  • There is a set of plugins for certain databases (MySQL and Oracle in version 1.x) and different database management utilities (e.g. ERD

You can find more from the link :

http://dbeaver.jkiss.org/about/

I just found this tool so easy to connect with Vertica DB and do some queries analysis

Summary:

Through this article, I am sure you are now able to understand how you can install Vertica Analytic Database in a single node and how you can use Vertica Management Console to monitor multiple clusters from a single point of access. How you can easily get the story behind your data in Vertica DB by using the Tableau Reporting tool.

Also, how you can use quires to do more detail analysis by using the DBeaver tool in Vertica Database.

More detail step by step full artice can be download from the link below :

https://www.slideshare.net/anil_maharjan/how-to-setup-vertica-in-a-single-node

Thanks,

Anil Maharjan

BI Engineer

http://np.linkedin.com/in/maharjananil

Using Power Query to tell your story form your Facebook Data.

Using Power Query to tell your story form your Facebook Data.

Most of the time of this weekend, I spent my time to extract the story behind my Facebook data by using Power Query. Power Query can connect data across a wide variety of sources, where facebook is one of the data source. By using Power Query you can extract your facebook data easily and do analysis your own story by using your own facebook data.

Talking about Power Query: Microsoft Power Query for Excel is an Excel add-in that enhances the self-service Business Intelligence experience in Excel by simplifying data discovery, access and collaboration.

Microsoft Power Query for Excel, is a new add-in that provides a seamless experience for data discovery, data transformation and enrichment for Information Workers, BI professionals and other Excel users. With Power Query you can:

  • Identify the data you care about from the sources you work with (e.g. relational databases, Excel, text and XML files, OData feeds, web pages, Hadoop HDFS, Facebook Data etc.).
  • Discover relevant data from inside(*) and outside your organization using the search capabilities within Excel.
  • Combine data from multiple, disparate data sources and shape it in order to prepare the data for further analysis in tools like Excel and Power Pivot, or visualization in tools like Power View and Power Map.

You can easily download from the link below:

http://www.microsoft.com/en-us/download/details.aspx?id=39379

You can find more about Power View, Power Map, Power BI and Q&A from the official Microsoft Power BI site. http://www.microsoft.com/en-us/powerBI/default.aspx

Now, I think you have installed Power Query add-in in Excel 2013 professional edition,

So, let’s drill into your Facebook data to extract your story .!!!

Firstly you need to open Excel sheet and go to the Power Query ribbon bar in Excel, then go to the ‘From Other Source’ section where you will see the ‘From Facebook option’ from dropdown list.

 

Due to the so many slides or pages, the detail step by Step guide have uploaded into my Slideshare account. You can download or go through the section in order to know how to use Power Query and how to extract your Facebook Data and how to do analysis using Power View to tell the story. Here is the Slideshare:

Slideshare link: http://www.slideshare.net/anil_maharjan/using-power-query-to-tell-your-story-form-your-facebook-data

Where I have use Power View to analyse the facebook data and give us some story to view.

Here I have used three Power Query Analysis, I think you will find these easy after you once go through the slides in above link where I have listed detail step by step method to do these analysis or if you have some knowledge of Power View then it must be quite easy to do analysis.

Power Query Analysis 1 using Power View:

  • Let’s see when you have added your friends.
  • Through this analysis you can find out the total friends added in facebook, in which year, month.
  • First and recent friends connected within facebook.
  • You can know when you are connected with which friend.

Happy to see the results. 🙂

Now you want to know first and recent friends connected within Facebook along with which time they have added . You can get it by using  period field in Tile By and story field in rows with friend added filter field in column . This shows the first friends that you have added in facebook. Happy to see the result. 🙂

If you need to find out the recent added friends in Facebook then just click the last period.

 

Here as per chart I can clearly see that I have added a lot of friends in 2009 and 2010. Since that is the year when I had completed my computer engineering course and most of my time I was spending in facebook. Quite amazing it does remind my past college life. Power Query along with Power View actually can tell us story of our own. Happy to find out my past.

This is such a cool tool, Power Query and Power View along with Power BI, You can visualize the things you just want to see.

Power Query Analysis 2 using Power View:

  • Ø Now, let’s see how many of your friends are male and female. 

I think after going through the slides you will surely know how to get the data from Facebook using Power Query for friend list and how to use Power View to analysis. You can go through detail steps again from slides to do this analysis.

Power Query Analysis 3 using Power View:

  • Let’s see your facebook activities from past to present.

This will help you to track down how much time have you spent in facebook and how frequent you post or update your feeds.

This will helps facebook to track down the facebook user usage trend.

  • How and when user is spending the time in facebook.
  • Why the facebook user usage trend is decreasing or increasing.
  • What is the reason behind the feeds data of user is decreasing or increasing.

For the facebook activities analysis, you can use the same feed information worksheet and by creating new Power View and line chart option you can start analysis of facebook activities from different perspective.

From graph we can clearly see that there is a huge spike in 200907 to somewhat up to 201003 this is still because this is the time when I had completed my computer engineering course and most of my time I was spending in facebook.

Also, going on next year in 201109 there is another spike which is due to that month is my birthday on 04thSeptember, where there is more feeds regarding my birthday wishes.

Now, let’s see the current year facebook activates by using similar Power View and same drag and drop features and by scroll down to last month date 201405 you can see the trend of your current facebook activities.

Here from line trend analysis it seems that the facebook activates from past year to current is frequently decreasing in my case. This is due to that I am spending less time in facebook as compares to previous year where I use to spend most of my time.

Reference:

http://www.microsoft.com/en-us/powerBI/default.aspx 

http://www.slideshare.net/murrayfife/using-microsoft-power-query-to-analyze-facebook-data

http://office.microsoft.com/en-001/excel-help/introduction-to-microsoft-power-query-for-excel-HA104003940.aspx

SUMMARY:

Power Query along with Power View actually can tell us story of your own. This is how Power Query along with Power View can tell your story by using your own facebook data. Happy to find out my past.

Quite amazing it does remind my past college life.

This is such a cool tool, Power Query and Power View along with Power BI, You can visualize the things you just want to see.

Also, I am thinking that if facebook provides our friends location data then it will be so easy to find out the friends and family around the world. Where Power Map will be more useful to track down the friends whom we have no idea where she or he is but we do connected with facebook in virtual world. If we can think of creating facebook friends and family location track app which will helps us to find out where exact is our friends and family are located based on the current location information of facebook data.

Thanks,

Anil Maharjan

How to transpose rows to columns in oracle.

In some case we need to transpose the table data that means we need to change the rows value into columns, and then this blog post will be surely helpful.

Let’s say we have to find out the trend of expenses of the customer or say some account no or mobile no for some few months to find out his or her behavior such as we have a data in table ‘Pivot_Table’  as

MobileNO TotalExpenses 	Date
1	3000	20140101
2	5000	20140101
3	7000	20140101
1	5000	20140201
3	9000	20140201
2	3000	20140201
1	1111	20140301
2	2222	20140301
3	3333	20140301

Now, in order to find out the behavior analysis for the certain months per MobileNO, we need the output such as

MobileNO JanExpenses	FebExpenses	MarchExpenses
1	3000 		5000		1111
2	5000		3000		2222
3	7000		9000		3333

This can be easily done by using the PIVOT function for Oracle version11.2

http://www.oracle-base.com/articles/11g/pivot-and-unpivot-operators-11gr1.php

But if your oracle version is old than 11.2 then you can use DECODE function to obtain such solution:

DECODE is a function in Oracle and is used to provide if-then-else type of logic to SQL. It is not available in MySQL or SQL Server. The syntax for DECODE is:

SELECT DECODE ( "column_name", "search_value_1", "result_1", 
["search_value_n", "result_n"],
{"default_result"} );

"search_value" is the value to search for, and "result" is the value that is displayed.

Here is my Query:

SELECT /*+parallel(t,4)*/
    MobileNO,
    	MAX(DECODE(Date, 20140101, TotalExpenses)) AS JanExpenses,
MAX(DECODE(Date, 20140201, TotalExpenses)) AS FebExpenses,
MAX(DECODE(Date, 20140301, TotalExpenses)) AS MarchExpenses
  FROM
    Pivot_Table t
GROUP BY MobileNO
ORDER BY MobileNO;

Hope this post will be helpful.

Thanks,

Anil Maharjan

Watch 24 Hours of PASS Business Analytics sessions if you had missed any.

From yesterday evening to till today morning I had a great time attending the ‘24 Hour of PASS Business Analytics’ free live 12 back-to-back webcasts from various BA Conference speakers.

If you have missed out to join the live sessions on 5th Feb 2014th which had begin at 08:00PT / 11:00 ET / 16:00 GMT, then no worries the recorded webcasts sessions is available now and you can just check out those great session and am sure you will get some great knowledge in the world of BA from a great speakers around the world.

http://www.sqlpass.org/bac/2014/Sessions/SneakPeeks.aspx

You will be able to know the story behind your own DATA through these great sessions.

Also, there is a contest #pass24Hop Challenge organized for this event, where anyone can take part in the challenges and if you are lucky enough and passionate about DATA and BI then you could get a chance to win a free ticket to2014 PASS Business Analytics Conference which is going to be held on San Jose, CA from May 7-9. I think this event will be a great success and will bring more new challenges and opportunity in the world of BI, BA. Wish I could also attend this event  🙂 !!!

http://blogs.technet.com/b/dataplatforminsider/archive/2014/01/24/join-the-2014-pass-business-analytics-24hop-challenge-and-you-could-win-a-free-ticket.aspx

http://www.sqlpass.org/bac/2014/Home.aspx

Thanks,

Anil Maharjan

Some Question and knowledge learn from PASS Summit 2013 Preview

The session’s was really great and so do the Speakers . Learn a lot off stuffs and knowledge from 24 hours for PASS SUMMIT Preview.http://www.bidn.com/blogs/Anil/ssas/4520/don-t-forget-to-join-the-session-s-by-expert-today-24-hours-of-pass-summit-preview

Here are some of the question I have asked so far and by doing some research on that I found some answers as:

1.Power view loads data in Memory, so how big data set can it load and will it be significant to work/analysis as similar to multidimensional OLAP cube analysis.?

: I could not get the clear answer of this one but I think Power view can’t be used as significant as SSAS providing .

2.Will SQL server going to handle the BIG data as similar like Hadoop and others.

Since they are quite different  product I know, Microsoft Big Data offers an integrated platform for managing data of any type or size, whether it is structured data from a relational data warehouse such as SQL Server 2012 Parallel Data Warehouse, unstructured data on Hadoop or streaming data.

PolyBase is a breakthrough new technology on the data processing engine in SQL Server 2012 Parallel Data Warehouse designed as the simplest way to combine non-relational data and traditional relational data in your analysis. So, MSSQL 2012 focusing  for Big DATA , structured/ Unstructured data as like Hadoop currently emerging.

http://www.microsoft.com/en-us/sqlserver/solutions-technologies/business-intelligence/big-data.aspx

http://www.microsoft.com/en-us/sqlserver/solutions-technologies/data-warehousing/polybase.aspx

3.Can Power view used to analyze the Multidimensional OLAP cube?

I think this feature is released in SQL Server 2012 SP1 CU 4 and still not works in Excel 2013 if I am not wrong.

http://blogs.msdn.com/b/analysisservices/archive/2013/05/31/power-view-connectivity-for-multidimensional-models-released.aspx

4.Is it better to use 50-60 left join from almost 50 tables having cluster index to populate a single fact table or we can use some intermediate table using 20-20-20 joins and lastly join those tables to populate a single fact table.

: still waiting for answer

5.what will be the performance issue while replication AlwaysON High Availability and how does it sync with primary to secondary .

:This is nice feature I notice about in SQL 2012.

6.what will be better approach vertical partitioning or horizontal partitioning in SQL 2012.

:Vertical partitioning since horizontal partitioning is quite complex in SQL 2012 as per speaker.

7.Does Power view and Power Map only support Bing maps, can’t we use a Google maps.

:No, we cannot use Google maps for now.

These are some of the question I have asked so far, and the answers may not be quite enough or correct.

I hope someone knows better answer for these question too so, if you have any please share some.

That will surely helps us and wish I could attend the PASS Summit 2013 that is going to held on

Charlotte, NC: October 15-18, 2013 .

http://www.sqlpass.org/summit/2013/

Thanks,

Anil Maharjan

Think while Rename a Column Name of Table in SQL.

If someone tries to rename the column of table then we write a query as

SP_RENAME  'TableName.[OldColumnName]' , '[NewColumnName]', 'COLUMN'
--we use as
SP_RENAME  'DimCustomer.[FirstName]' , 'CustomerName' , 'COLUMN'
--Mistakenly,
SP_RENAME  'DimCustomer.[FirstName]' , '[CustomerName]' , 'COLUMN'
which gives the column the name [CustomerName], not CustomerName.
For rename the column 
http://blog.sqlauthority.com/2008/08/26/sql-server-how-to-rename-a-column-name-or-table-name/

You don’t use the [] escape in the second argument but by mistakenly if we use ‘[CustomerName]’ as new column name instead of CustomerName.

It sucessfully updates the column but while we select that column it’s now gives the error as

"Either the parameter @objname is ambiguous or the claimed @objtype (COLUMN) is wrong"

Also while we try to drop the column itself we cannot delete it. It also gives error as

ALTER TABLE DROP COLUMN failed because column '[CustomerName]' does not exist in table 'Customer'.

Does that mean we have to recreate the customer table since the renamed column [CustomerName] cannot use in select statement too.

Well there is a method for changing the mistakenly rename column ‘[CustomerName]’ to CustomerName . Just simple you need to go to the table DimCustomer and then click plus sign of the table and then you can see the list of columns of DimCustomer table then right click and add New Column tab which will edit all the list of columns and there you go just change the ‘[CustomerName]’ to CustomerName . This will solve the issue we are having.

columnrename

If someone by mistakenly rename the column as above and getting similar issue this post will be surely helpful.

Also, there might be any alternative T-SQL method then please suggest some idea that will surely helpful.

Thanks,

Anil Maharjan

How can we know and free remote login session in Windows Server 2008 ?

Through this post , I want to share about the problem we faced while working in Windows Server 2003/2008.

In our day to day work , we might frequently need to connect to remote Windows Server machine and while we try to connect to the Windows Server which allows multiple login session, sometimes we get an error message as : if there is no any free sessions “

If you need to connect the server urgently for your high priority task to complete then you simple need to find the sessions in that server and free one session for your login access and to complete your job. Also, Most of the already logged in users won’t even be using the session at the time and there might be some session which has been disconnected for a long time.

How can we know remote login session in Windows Server 2008?

For this,  we need to find out the RDP login sessions both active/disconnected in that particular Windows Server machine. First of all you must be logged in to your client computer as a domain Admin user and then You can use the query from the command line as:

C:\>query session /server:SERVERNAME
Or
C:\>qwinsta /server:SERVERIP

You can simple type the Server IP Address or Server Name and you will get the both active and disconnected session in that particular server as:

session

How can we free remote login session in Windows Server 2008?

From the above we can see that ahal user has a disconnected session ID#2 , so we can free this session if that user has not been doing any work in that server . you need to make sure that user hasn’t running or doing any work in offline or disconnected mode for this you can simple chat with him or just inform.Otherwise, if maharjan user  finishes his work and still in active state then you can kill one of these two session by using the query below.

C:\>reset session 2 /server: SERVERIP 
Or
C:\>rwinsta /server: SERVERIP SESSIONID

For killing the session ID#2 for ahal user terminate .

If you want to see additional information you can run the command above with [/v]

C:\>reset session 2 /server: SERVERIP  /v

Resetting session ID 2
Session ID 2 has been reset

Now , you can check the sessions in that server by simple using C:\>qwinsta /server:SERVERIP and make sure the user session is terminated and do the needful job by connecting your own session.

Hope this blog post might help for you who faces such problem in our day to day work.

Thanks,

Anil Maharjan

Date Range for SQL and Oracle.

Hello all,

This will be my memorable blog post for this year. Since, this is my last blog post for this year acc to Nepali year.We are celebrating happy new year(01/01/2070 B.S) day after tomorrow :). so, want to wish you all happy Nepali new year.

This blog is related to the Date Range. While working I need to generate the date range for Oracle side date range from start date to enddate(i.e. 2010-01-01 to 2012-01-01) . Though it’s easy for me in SQL side but in oracle side I have to do some research for this since I am not an Oracle guy.

I think there might be different alternative way for this but If you want to generate the date range from startdate to enddate, then you can simply run the script as below.

For SQL Date Range script:

--startdate: 2010-01-01 enddate :2012-01-01
IF EXISTS(SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'DateRange') 
DROP TABLE DateRange
GO
CREATE TABLE [dbo].[DateRange](
	DateRange DATE
)
GO
DECLARE @StartDate Date;
DECLARE @EndDate Date;
SET @StartDate='2010-01-01'
SET @EndDate='2012-01-01'
WHILE (@StartDate<@EndDate)
BEGIN
 INSERT INTO  DateRange
 VALUES(@StartDate)
 SELECT @StartDate = DATEADD(month,1,@StartDate)--for month wise increment
 --SELECT @StartDate = DATEADD(year,1,@StartDate)--for year wise 
-- SELECT @StartDate = DATEADD(day,1,@StartDate) --for day wise
END
GO
--SELECT * FROM DateRange

For Oracle Date Range Script:

DECLARE
   does_not_exist   EXCEPTION;
   PRAGMA EXCEPTION_INIT (does_not_exist, -942);
BEGIN
   EXECUTE IMMEDIATE 'DROP TABLE DateRange';
EXCEPTION
   WHEN does_not_exist
   THEN
      NULL;
END;
/      
  CREATE TABLE DateRange (
  DateRange DATE 
)   
/                      
DECLARE
    vblStartdate DATE;
    vblEnddate DATE;
BEGIN 
    vblStartdate := To_Date('2010-01-01','YYYY-MM-DD');
    vblEnddate := To_Date('2012-01-01','YYYY-MM-DD');

    WHILE (vblStartDate <vblEndDate) 
    LOOP
EXECUTE IMMEDIATE 'INSERT INTO DateRange SELECT '''||vblStartDate||''' FROM DUAL';
EXECUTE IMMEDIATE ('SELECT Add_Months('''||vblStartDate||''',1) FROM DUAL')INTO  vblStartDate;
    END LOOP;
END;
/   
--select * from DateRange

OUTPUT:

DateRange -- month wise increment from startdate to enddate
2010-01-01
2010-02-01
2010-03-01
2010-04-01
2010-05-01
..........
2011-12-01

I think this post will be helpful for some of us who is looking for such date range.

Once again Happy Nepali New YEAR 🙂 !!!

Thanks,

Anil Maharjan

The power of SIMBA into the world of BI on top of BIG DATA

After a long time gap, I would love to post this blog regarding the power of SIMBA  into the world of BI on top of BIG DATA .

It’s really interesting and exciting to see these kinds of new technology and tools on the way. Well ,I am talking about the SIMBA MDx Provider developed by the simba Technologies .

Yesterday, while I was researching regarding BIG DATA and reading some great blog post then I came to know about the SIMBA MDx Provider which seems to be a great and cool tool .

What actually the Simba’s MDx Provider :

Simba’s MDX Provider is an ODBO provider installed on the same machine as Excel. Simba also has a tool for building cube definitions, which we call schemas. These schemas are saved in XML. Simba’s schema maps MDX metadata constructs to Impala table structures. When an ODBO compliant tool such as Excel issues an MDX query, Simba’s MDX Provider maps the MDX query to HiveQL, sends the HiveQL to Impala, collects the results and returns them to the end user.

The most important technical concept is that there is no intermediate server or cube structure that caches data, all queries go direct from Simba’s MDX Provider to the Cloudera Impala server in real time.

You can find out more on this through the main blog post by samba itself through the link below:

http://blogs.simba.com/simba_technologies_ceo_co/2013/02/demo-microsoft-excel-pivottables-on-cloudera-impala-via-simba-mdx-provider.html

As the technology is in early development, it is not generally available for early testing.

Demo of Simba’s integration of doing MDX queries over Cloudera’s Impala for use with Microsoft Excel Pivot Tables .http://youtu.be/kZahPE9Puv0

Also there is a great PDF regarding the Simba Teradata case- study.

http://www.simba.com/docs/Teradata-Case-Study.pdf

Hope to see and use these kind of great tools in near future into our world of BI.

Reference :

http://blogs.simba.com/simba_technologies_ceo_co/2013/02/demo-microsoft-excel-pivottables-on-cloudera-impala-via-simba-mdx-provider.html

http://blog.cloudera.com/blog/2012/10/cloudera-impala-real-time-queries-in-apache-hadoop-for-real/

http://cwebbbi.wordpress.com/2013/02/25/mdx-on-cloudera-impala/

Thanks,

Anil Maharjan

SQL PASS Summit 2012 Hot News !!!

Hello all,

Some Hot news of the SQL PASS SUMMIT 2012.

PASS Summit is the world’s largest, most-focused, and most-intensive conference for Microsoft SQL Server and BI professionals.

Organized by and for SQL Server and BI users, PASS Summit delivers the most technical sessions, the largest number of attendees, the best networking, and the highest-rated sessions and speakers of any SQL Server event in the world.

The 3 most awaited days from Nov 7-9 are here for all the SQL and BI lovers around the world.

Currently, I couldn’t attend this session but I wish to attend this session in upcoming days or in near future summit’s a long run way to go .No worries, PASS Live TV is here for us who couldn’t make it to this SUMMIT in Seattle. The live covering of the SUMMIT can be browsed through the link below.

http://www.sqlpass.org/summit/2012/

Also, it’s nice to hear the upcoming project news from the Summit Keynotes .Apart from that ,I have read few blogs regarding the Summit Keynotes  from the great persons and want to share with you guys too .So that one can know what’s in upcoming future technologies and updates in SQL and BI field mainly.

These are some most highlighted upcoming updates in our field. You can have a look on these from the great writer blogs ( By Chris ,Marco and Denny ).

  • Project Hekaton: Project Hekaton,codename for in-memory OLTP technology with improvement in performance and scalability.
  • Polybase : Polybase allows you to query data from an external source from within the SQL Server 2012 PDW.  This will allow you to create an external table which simply links to a Hadoop cluster.By using Polybase a single T-SQL query will run queries across relational data and Hadoop data. A single query language for both. Sounds really interesting for using BigData in a more integrated way with existing relational databases.

By Chris “Wouldn’t it be cool if you could query the Facebook graph or Twitter or even Bing directly from TSQL?”Really looking forward to see these kinds of upcoming project by Microsoft SQL Server Team.

  • Updates on Columnstore: In the next major release of SQL Server the columnstore indexes will be updatable and it will be possible to create a clustered index with Columnstore index. This is really a great news for near real-time reporting needs.
  • Power View works with Multidimensional cube
  • Microsoft HDInsight Server CTP which is Microsoft’s Hadoop offering
  • SQL Server 2012 SP1 and Office 2013
  • Mobile BI and more

You can have a look in the links below for more details .

http://sqlblog.com/blogs/marco_russo/archive/2012/11/07/pass-summit-2012-keynote-and-mobile-bi-announcements-sqlpass.aspx

https://cwebbbi.wordpress.com/2012/11/08/thoughts-on-the-pass-summit-2012-day-1-keynote/#comment-6615

http://itknowledgeexchange.techtarget.com/sql-server/sql-pass-day-1-keynote/

Just want to share the updates with you guys.we will be looking forward to play around with these great products and features from Microsoft.

Thanks,

Anil Maharjan