Untitled

BID ------------------
//upload.jsp


<%@page import="java.io.FileInputStream"%>
<%@page import="java.io.InputStream"%>
<%@page import="java.io.IOException"%>
<a href="upload.jsp"></a>
<%@page import="java.io.File"%>
    <%@page import="java.sql.*"%>
<%@page contentType="text/html" pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
    <head>
        <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
        <title>JSP Page</title>
    </head>
    <body>
        <%

            String fname=request.getParameter("name");
            String lname=request.getParameter("phoneno");
            String email=request.getParameter("emailid");

            String about=request.getParameter("aboutme");

            File theFile = new File(request.getParameter("image"));
            //FileInputStream input = new FileInputStream(theFile);
            out.println("FullPathname" + " " + theFile.getAbsolutePath());
            //    out.println(fname);


            try {

                        Class.forName("net.ucanaccess.jdbc.UcanaccessDriver");

                        //net.unanaaccess.

                            //String f1="C:/fakepath/img-807080652-0001.jpg";
                        String msAccDB = "E:/BI/Student.accdb";
                        String dbURL = "jdbc:ucanaccess://" + msAccDB;

                        // Step 2.A: Create and get connection using DriverManager class
                       Connection c = DriverManager.getConnection(dbURL);

                       out.println("connected");
                        // Step 2.B: Creating JDBC Statement
                        Statement s = c.createStatement();
                        String f="insert into student values('"+fname+"','"+lname+"','"+email+"','"+about+"','"+theFile.getAbsolutePath()+"')";
                        s.executeUpdate(f);
                        out.println("inserted");

            }

            catch(Exception e)
            {

            }

        %>
        <a href="display.jsp">show records</a>
    </body>
</html>


        Business Intelligence and Big Data Analytics


Database Creation

Table: Employee


Column Name Data type
Employee_ID (PK)    AutoNumber
Employee_Name   Text
Manager_ID  Number

Table: Product

Column Name Data type
Product_ID (PK) Number
Brand_Name  Text
Product_Name    Text
Price   Currency
Subcategory_ID  Number


Table: Product Category

Column Name Data type
Category_ID (PK)    AutoNumber
Category    Text


Table: Product Subcategory

Column Name Data type
SubCategory_ID (PK) AutoNumber
Subcategory Text
Category_Manager    Text
Category_ID Number


Table: SalesFact

Column Name Data type
Month (PK)  Date/Time
State_ID (PK)   Number
Product_ID (PK) Number
Employee_ID (PK)    Number
Sales_Dollars   Currency
Sales_Units Number

Table: State

Column Name Data type
State_ID (PK)   Number
State_Name  Text
Region  Text
Country Text


Note: Insert at least 10 records in each table.


Practical No: 01

Create a database application that takes
• Structure data
• Unstructured data
• Semi-Structured data

Index.html
<!DOCTYPE html>
<!--
To change this license header, choose License Headers in Project Properties.
To change this template file, choose Tools | Templates
and open the template in the editor.
-->
<html>
    <head>
        <title>TODO supply a title</title>
        <meta charset="UTF-8">
        <meta name="viewport" content="width=device-width, initial-scale=1.0">
        <script type="text/javascript">
        function showFileName() {
           var fil = document.getElementById("myFile");
           alert(fil.value);
        }
        </script>
    </head>
    <body>
        <form name="form1" method="post" action="upload.jsp" enctype="multipart/form-data">
 Name <input type="text" name="name" value="" required/>
            <br><br><br>
            Phone Number <input type="text" name="phoneno" value="" required />
            <br><br><br>
            Email id <input type="email" name="emailid" value="" required="" />
                       <br><br><br>

            About you <textarea name="aboutme" rows="4" cols="20" required>

            </textarea>
                        <br><br><br>

            Upload Image<input type="file" id="myFile" name="image" ACCEPT="image/gif,image/jpeg,image/jpg,image/tiff,image/tif" required/>
             <a href="#" onclick="showFileName()">Show Name</a>            <br><br><br>
            <input type="submit" value="Submit" name="submit" />
</form>
    </body>
</html>
Upload.jsp
<%--
    Document   : upload
    Created on : 4 Apr, 2018, 4:07:31 PM
    Author     : shreyash
--%>

<%@page import="java.io.FileInputStream"%>
<%@page import="java.io.InputStream"%>
<%@page import="org.apache.commons.codec.binary.Base64"%>
<%@page import="java.io.IOException"%>
<a href="upload.jsp"></a>
<%@page import="java.io.File"%>
    <%@page import="java.sql.*"%>
<%@page contentType="text/html" pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
    <head>
        <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
        <title>JSP Page</title>
    </head>
    <body>
        <%

            String fname=request.getParameter("name");
            String lname=request.getParameter("phoneno");
            String email=request.getParameter("emailid");

            String about=request.getParameter("aboutme");

            File theFile = new File(request.getParameter("image"));
            FileInputStream input = new FileInputStream(theFile);
            try {

                        Class.forName("net.ucanaccess.jdbc.UcanaccessDriver");
                String msAccDB = "F:/mongodb/Student.accdb";
                        String dbURL = "jdbc:ucanaccess://" + msAccDB;

                        // Step 2.A: Create and get connection using DriverManager class
                       Connection c = DriverManager.getConnection(dbURL);

                       out.println("connected");
                        // Step 2.B: Creating JDBC Statement
                        Statement s = c.createStatement();
                        String f="insert into student values('"+fname+"','"+lname+"','"+email+"','"+about+"','"+theFile.getAbsolutePath()+"')";
                        s.executeUpdate(f);
                        out.println("inserted");

            }

            catch(Exception e)
            {

            }

        %>
        <a href="display.jsp">show records</a>
    </body>
</html>
Display.jsp
<%--
    Document   : display.jsp
    Created on : 22 Apr, 2018, 10:35:22 AM
    Author     : shreyash
--%>

<%@page import="java.sql.ResultSet"%>
<%@page import="java.sql.DriverManager"%>
<%@page import="java.sql.Connection"%>
<%@page import="java.sql.*"%>
<%@page contentType="text/html" pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
    <head>
        <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
        <title>JSP Page</title>
    </head>
    <body>
        <%
            try {
         Class.forName("net.ucanaccess.jdbc.UcanaccessDriver");
                        String msAccDB = "F:/mongodb/Student.accdb";
                        String dbURL = "jdbc:ucanaccess://" + msAccDB;

                        // Step 2.A: Create and get connection using DriverManager class
                       Connection c = DriverManager.getConnection(dbURL);

                       out.println("connected");
                       // Step 2.B: Creating JDBC Statement
                          out.println("<table border=1>");
                 out.println("<tr><th>ename</th><th>phone no</th><th>emailid</th><th>aboutme</th><th>path</th></tr>");

                       Statement s = c.createStatement();

                        String f="select * from student";
                        ResultSet rs=s.executeQuery(f);

                        while(rs.next()) {
                            out.println("<tr><td>"+rs.getString(1)+"</td><td>"+rs.getString(2)+"</td><td>"+rs.getString(3)+"</td><td>"+rs.getString(4)+"</td>"+"</td><td>"+rs.getString(5)+"</td></tr>");


                        }
                        out.println("</table>");
            }

            catch(Exception e)
            {

            }

        %>
    </body>
</html>


Practical No: 02

Title: Creating a Data Warehouse

Problem Statement: Create and schedule a DTS Package using Data Transformation services (DTS) tool. Fire at least 5 queries on the database.

Description:

Creating a DTS Package with the DTS Import/Export Wizard

The Data Transformation Services (DTS) Import/Export Wizard offers the simplest method of building a DTS package, interactively guiding you through the process of copying and transforming data. Following are the basic steps for creating a package with the DTS Import/Export Wizard:

1.  Open SQL Server Enterprise Manager, expand the server, and click the Databases folder.

2.  On the Tools menu, point to Data Transformation Services, and then click Import Data.

3.  In the Choose a Data Source dialog box, select Microsoft Access as the Data Source, and then type the path and file name of your Access database (.mdb), or use the browser to browse for the file.


4.  In the Choose a Destination dialog box, select Microsoft OLE DB Provider for SQL Server, and then select the database server and the required authentication mode. In the Database dialog box, select the target database on the server.
5.  In the Specify Table Copy or Query dialog box, click Copy tables and views from the source database. If you copy the data or the results of a query, you can customize the data being copied to the destination. You can:
• Select which source or destination columns to copy.
• Select which source or destination columns to ignore.
• Change the data type where valid.
• Define how the data is to be converted between source and destination.


6.  In the Select Source Tables and Views dialog box, select the tables and queries to import.
7.  In the Save, Schedule, and Replicate Package dialog box, select Run Immediately.
8.  Click Finish.

Cube Creation

Create a new OLAP database

1.  Open Programs  Microsoft SQL Server  Analysis Services  Analysis Manager.

2.  Right click the server in the console tree, and click New Database to display the Database dialog Box.

3.  Type Sales as the database name, and type Market Database as the description.

4.  Click OK to close the dialog box and to create the database.

Specify a data source

1.  Expand the Sales database folder. Right-click the Data Sources folder, and on the shortcut menu, click New Data Source.

2.  Select Microsoft Jet 4.0 OLE DB Provider, and click Next.

3.  On the connection Tab of the Data Link Properties dialog box, click the ellipsis button(…), navigate to the folder containing the Sales.mdb

4.  Click Test Connection to make sure the data source definition is correct. Then click OK to close the dialog box. Expand the Data Sources folder to see the New Data Source.

5.  Right-click the New Data Source, and click Copy on the shortcut menu. Right-click the Sales database, and click Paste on the shortcut menu. When asked for a new name, click Delete on the shortcut menu, and click Yes to confirm.


Designing a Cube by Using the Cube Wizard

Select the Fact Tables and the Measures:

1.  Right Click the Cube folder under the Sales database, point to New Cube, and then click Wizard to start the Cube Wizard. On the Welcome screen, click Next.


2.  Select SalesFact from the list of tables.


3.  Click Browse Data to review the records in the table. Close the Browse Data window.

4.  After selecting the fact table, click Next to move to the next step of the wizard.

5.  Add the Sales_Dollars and Sales_Units columns as measures by double-clicking each in turn. You can select a measure and click the right arrow button(>).


6.  After adding both measures, click Next.

Create a dimension from a Star Schema table:

1.  Click a New Dimension in the Wizard to start the Dimension Wizard. Select the option to skip the welcome screen, and then click on Next.


2.  Click the Option Star Schema: A Single Dimension Table, and the click Next.


3.  In the Select “The Dimension Table Screen”, select State from the Available Tables list.


4.  After selecting the dimension table, click Next.

5.  Double-click Country, Region, and State_Name in the order. You select the Levels from the most summarized to the most detailed.


6.  Click Next three times. Type State as the name of the dimension. Before closing the wizard you can preview the dimension hierarchy.


Create a dimension from Snowflake schema tables:

1.  Click Next Dimension in the Cube wizard to again launch the Dimension Wizard.

2.  Click the option “Snowflake Schema: Multiple, Related Dimension Tables”, and Click Next.

3.  Double-click the dimension tables in the following order: Product, Product Subcategory, and Product Category.


4.  Click Next.

5.  Review the joins between tables. If necessary add join between the two Subcategory_ID columns and the two Category_ID columns, and click Next.


6.  Double-click Category, Product_Name, and Subcategory intentionally reversing the order of the last two levels. The Dimension Wizard displays a message suggesting that you can rearrange the levels.


7.  Click Yes to put the Product_Name level below the Subcategory level.

8.  Click Next three times.

9.  Type Product as the name of the dimension, expand items in the Preview window as desired, and click Finish.


Create a dimension from Parent-Child tables:

1.  Click Next Dimension in the Cube wizard to again launch the Dimension Wizard.

2.  Click Parent-Child: Two Related Columns In a Single Dimension Table, and click Next.

3.  Select Employee as the dimension table, and click Next.


4.  In the Member Key drop-down list, select Employee_ID. This is the column that is joined to the fact table. In the Parent Key drop-down list, select Manager_ID. In the Member Name drop-down list, select Employee_Name.


5.  Click Next twice: once after defining the parent-child hierarchy, and once to bypass the advanced options step.

6.  Type Employee as the name of the dimension.

7.  Click Finish.


Create a “Time” dimension from a date column:

1.  Click Next Dimension in the Cube wizard to again launch the Dimension Wizard.

2.  Click the Option Star Schema: A Single Dimension Table, and the click Next.

3.  In the Select “The Dimension Table Screen”, select SaleFact from the Available Tables list. Because the SaleFact table contains a Date/Time column, the Wizard displays a screen offering to create a Time dimension based on that column.


4.  Select the dimension type, and then click the Time Dimension.

5.  The Date column drop-down list allows you to choose which Date column to use if there is more than one in the table. Select Month and click Next.


6.  On the screen that asks you to create the Time dimension levels, you can select the type of Time dimension hierarchy you want. You can even specify the Day and Month for the Year to start – useful for dealing with the fiscal years. In the select time levels drop-down list, click “Year,Quarter,Month” and then click Next.


7.  When you use the Wizard to generate the date related levels you do not select columns to use for the levels. Click Next to bypass the “Advanced Option Screen”.

8.  In the final screen on the Dimension Wizard, type “Time” as the dimension name.

Name, Save and Process a Cube:

1.  In the Cube Wizard click Next, click Yes when asked if you want to count the fact table rows.


2.  Type “SalesCube” as the name of the Cube and click Finish.


3.  Click Exit on the File menu to close the Cube Editor. Click No when asked if you want to set storage option.


4.  Expand the Cubes folder of the Sales database. Right-click the newly created SalesCube and click Process.

5.  Click OK to begin processing.


Working with Measures in the Cube

(To create a derived measure)

1.  Right-click on SalesCube and select Edit.

2.  In the SalesCube cube, right-click the Measures folder and click New Measure. Select the Sales_Dollars column, and click OK. The measure is added with the name Sales_Dollars1.


3.  Change the Source Column property to “SalesFact”.”Sales_Dollars”*0.15, and type Commission for the Name property.


4.  On the Tools menu, click Process Cube, agree to save the cube, decline design aggregates, and click OK when asked to specify the processing method. Close the Process log window, and browse the data.

5.  Scroll the grid as needed to see the new Commission measure.


(To specify the aggregation function for a measure)

1.  In the Cube Editor, Click the Insert Measure toolbar button, and click double-click the Sales_Units column. This creates a measure named Sales_Units1. Type Bad Price for the measure’s Name property, and change the value of the Source Column property to “SalesFact”.”Sales_Dollars” / ”SalesFact”.”Sales_Units”. This is a derived measure. It uses the default aggregation function: Sum.

2.  Process the Cube – that is, click the Process Cube button, save the cube, don’t design aggregates, and choose the full process method. Close the Process log window, and browse the data.

3.  Change the name of the Bad Price to Min Price, and change the value of the Aggregate function to Min.

4.  On the Tools menu, click Process Cube.


(To create a calculated measure)

1.  In the Cube tree, right-click the Calculated Members folder, and click New Calculated Member. In the Calculated Member dialog box, type Net Price as the new member name.

2.  In the data tree, fully expand the Measures dimension and double-click the Sales Dollars measure to make the member name appear in the Value Expression box. Click the slash ( / ) on the right side of the dialog box, and then double-click Sales Units in the Data tree.


3.  Click OK.


Member Property Creation and new Dimension Creation

To create Member Properties:

1.  In the Analysis Manager tree pane, Right Click on the Shared Dimensions folder.

2.  Select Editor.

3.  Select the Product dimension.


4.  Click OK.


5.  Right Click on the field “Brand_Name” in the Product Dimension and select Insert As Level.

6.  Right Click on the field “Product_Name” in the Product Dimension and select Insert As Level.

7.  Right Click on Product_Name level in the left-pane and select New Member Property.


8.  In the Insert Member Property dialog box select Price.

9.  Click OK.

10. Click on File Save. Give new dimension name as Product_M.

11. Click on File Exit.

12. Right Click on SalesCube. Select Edit.

13. In the left-pane of the Cube Editor Right-Click on Dimensions folder. Select Existing Dimensions. Select Product_M and press the button (>). Click OK.


14. Click on Save button. Click on Process button. Click No. Select Full Process. Click on OK.

15. Click File Exit.

Firing queries on the Cube by using MDX Sample Application

1.  Launch the MDX Sample Application, which is on the Analysis Services menu along with Analysis Manager. Click OK to connect to the server. Click the New Query File toolbar button to create a new query file. A query file can contain multiple queries.

2.  In the toolbar, select Sales as the database name. Then, in the Cube drop-down lost box, select SalesCube as the cube-name.


Note: MDX Sample application is divided into three vertical panes: the top pane is the query pane, where one builds an MDX query statement. The middle pane is the metadata pane, where one inspects information in a selected cube. The bottom pane is the results pane, which shows the output of an MDX query.


3.  Replace the contents of the query pane with SELECT FROM SalesCube.


4.  Click the Run Query button on the toolbar.

5.  Similarly, the following queries can be tried out:


Query1:

    SELECT [Product].[Category].Members ON COLUMNS FROM SalesCube


Query2:

    SELECT {[Sales Dollars],[Sales Units]} ON COLUMNS FROM SalesCube


Query3:

SELECT Country.Members ON COLUMNS, Category.Members ON ROWS from SalesCube


Query4:

SELECT Crossjoin(Country.Members,Category.Members) ON COLUMNS From SalesCube


Query5:

SELECT [Measures].Members ON COLUMNS, [Product].[Subcategory].Members ON ROWS From [SalesCube]