Austinslik's Blog

Computer shutdown timer

Posted by austinslik on November 21, 2010

There are times when I’m just too tired to seat and wait for my downloads or a running program to finish before i turnoff my computer. Search the internet, i found Stevepuri shutdown timer created in Windows Forms (i learned a lot from his application).I decided to create one myself since I’m learning WPF (Windows presentation foundation).

Computer shutdown timer allows you to run Windows power options (Hibernate,LogOff,Restart,PowerOff,Sleep and Shutdown) by selecting a date and time. If the checkbox “Force shutdown” is clicked, the option Force shutdown will be added to the dropdown list. choosing another power option will remove the “Force shutdown” option.

WARNING: The “Force Shutdown” will close all running program. All unsaved data will be lost.

Thanks to mentalist.org for providing the windowsController.cs class file.

Here is the setup file for installation.

timer Image

To force shutdown, check the “Force shutdown” checkbox and option will appear after accepting the warning Info about forcing shutdown

Source Code

namespace Computer_ShutDown
{
    public partial class Window1 : Window
    {
        #region Fields

        DispatcherTimer _timer;
        DispatcherTimer _dateTimeNow;
        WindowsController _winController;
        ObservableCollection<string> _dropDownValue;

        #endregion

        #region Constructor

        public Window1()
        {
            InitializeComponent();
            _timer = new DispatcherTimer();
            _dateTimeNow = new DispatcherTimer();
            _winController = new WindowsController();
            _dropDownValue = new ObservableCollection<string>();
            powerOptionsNames();
            _optionCombo.ItemsSource = dropDownValue;
            dateTimePicker.SelectedDateTimeChanged +=
                new DateTimeSelectedChangedRoutedEventHandler(dateTimePicker_SelectedDateTimeChanged);
            this.Icon = BitmapFrame.Create(
                new Uri("pack://application:,,,/Resources/press.png", UriKind.RelativeOrAbsolute));

            #region _optionCombo.SelectionChanged +=
            _optionCombo.SelectionChanged +=
                new SelectionChangedEventHandler(delegate(object o, SelectionChangedEventArgs e)
                {
                    if (_ForceShutdown.IsChecked == true && _optionCombo.SelectedIndex != 5)
                    {
                        _ForceShutdown.IsChecked = false;
                        _dropDownValue.RemoveAt(5);
                    }
                    _shutdownTimeTxt.Text += ":";
                });

            #endregion
        }

        #endregion

        #region Properties
        public ObservableCollection<string> dropDownValue
        {
            get { return _dropDownValue; }
        }
        #endregion

        #region powerOptionsNames
        private void powerOptionsNames()
        {
            foreach (string item in Enum.GetNames(typeof(PowerOptions)))
            {
                if(item != "ShutDown")
                _dropDownValue.Add(item);
            }
        }

        #endregion

        #region ShutDown

        public void ShutDown()
        {
            //close the application before power options
            Application.Current.Shutdown();

            //if checked force shutdown else do the rest
            if (_ForceShutdown.IsChecked == true)
            {
                WindowsController.ExitWindows(PowerOptions.ShutDown, true);
            }
            else
            {
                switch (this._optionCombo.SelectedIndex)
                {
                    case 0:
                        WindowsController.ExitWindows(PowerOptions.LogOff, false);
                        break;
                    case 1:
                        WindowsController.ExitWindows(PowerOptions.Restart, false);
                        break;
                    case 2:
                        WindowsController.ExitWindows(PowerOptions.PowerOff, false);
                        break;
                    case 3:
                        WindowsController.ExitWindows(PowerOptions.Hibernate, false);
                        break;
                    case 4:
                        WindowsController.ExitWindows(PowerOptions.Sleep, false);
                        break;
                }
            }
        }

        #endregion

        #region dateTimePicker_SelectedDateTimeChanged

        private void dateTimePicker_SelectedDateTimeChanged(
            object sender,
            DateTimeSelectedChangedRoutedEventArgs e)
        {
            this._dateTimeNowTxtBlock.Text = DateTime.Now.ToString();
            this._shutDownTime.Text = e.NewDate.ToString();

        }

        #endregion

        #region IsValidInput

        private bool IsValidInput()
        {
            if (dateTimePicker.DateTimeSelected.CompareTo(DateTime.Now) < 0)
            {
                if (CustomMessageBox.Show(
                    _optionCombo.SelectedValue +
                    " time must be in the future. \n" +
                    "Please select another Date / Time", "Warning",
                    CustomMessageBoxButton.Yes,
                    CustomMessageBoxImage.Error,
                    "   OK  ", "") == CustomMessageBoxResult.OK)
                { this.dateTimePicker.DateTimeSelected = DateTime.Now; }

                return false;
            }
            else
                return true;
        }

        #endregion

        #region Invoke_Click

        private void Invoke_Click(
            object sender,
            RoutedEventArgs e)
        {
                if (IsValidInput())
                {
                    this._ForceShutdown.IsEnabled = false;
                    this.dateTimePicker.IsEnabled = false;
                    this._optionCombo.IsEnabled = false;
                    this._invokeBtn.IsEnabled = false;
                    this._shutDownTime.Text = new DateTime(
                        dateTimePicker.DateTimeSelected.Year,
                        dateTimePicker.DateTimeSelected.Month,
                        dateTimePicker.DateTimeSelected.Day,
                        dateTimePicker.DateTimeSelected.Hour,
                        dateTimePicker.DateTimeSelected.Minute,
                        dateTimePicker.DateTimeSelected.Second).ToString();
                    _timer.Start();
                    _timer.Interval = new TimeSpan(0, 0, 1);
                    _timer.Tick += new EventHandler(delegate(object s, EventArgs a)
                    {
                        if (this._dateTimeNowTxtBlock.Text == dateTimePicker.DateTimeSelected.ToString())
                        {
                            _timer.Stop();
                            _dateTimeNow.Stop();
                            ShutDown();
                        }
                    });
                }
            }

        #endregion

        #region Cancel_Click

        private void Cancel_Click(
            object sender,
            RoutedEventArgs e)
        {
            Application.Current.Shutdown();
        }

        #endregion

        #region Window_Loaded

        private void Window_Loaded(
            object sender,
            RoutedEventArgs e)
        {
            _dateTimeNow.Start();
            _dateTimeNow.Interval = new TimeSpan(0, 0, 1);
            _dateTimeNow.Tick += new EventHandler(delegate(object s, EventArgs a)
            {
                this._dateTimeNowTxtBlock.Text = DateTime.Now.ToString();
            });
            _shutdownTimeTxt.Text += ":";
        }

        #endregion

        #region _bruteCheckBox_Click

        private void _bruteCheckBox_Click(
            object sender,
            RoutedEventArgs e)
        {
            if (_ForceShutdown.IsChecked == true)
            {
                if (CustomMessageBox.Show(
                    "WARNING: Forcing Shutdown. \n\n" +
                    "All running programs will be closed\n\n" +
                    "Do you want to continue?", "Force Shutdown",
                    CustomMessageBoxButton.YesCancel,
                    CustomMessageBoxImage.Warning,
                    "OK", "Cancel") != CustomMessageBoxResult.OK)
                {
                    _ForceShutdown.IsChecked = false;
                    _optionCombo.SelectedIndex = 0;
                    return;
                }
                else
                {
                    _dropDownValue.Add("Force ShutDown");
                    _optionCombo.SelectedIndex = 5;
                }
            }
            else
            {
                _dropDownValue.RemoveAt(5);
                _optionCombo.SelectedIndex = 0;
            }
        }
        #endregion
    }
}

Posted in Microsoft .Net, WPF | Tagged: | Leave a Comment »

Get Datetime difference wpf-c#

Posted by austinslik on March 7, 2010

Here is a straight forward way to get the difference between a given begin date and end date (Hour(s), Day(s), Week(s) ISO/US, Month(s), Quarter(s) and Year(s)) in Wpf c#. This can be done in so many ways to accomplish a certain task. This was needed for Reporting GUI. If you have a similar kind of task this might help. I have to say it’s not that obvious on how to do this in c# but with this I hope someone don’t have to suffer just to get e.g. number of weeks(ISO or US) between a given two date(month/year). The method GetDateTimeDiff(…) with parameter of two datetime 1. beginYear, beginQuater, beginMonth, beginWeek, beginDay, beginHour 2. endYear, endQuater, endMonth, endWeek, endDay, endHour. Zero any parameter you don’t need e.g. you want to get weeks between a given week number of a year like 50th week of 2009 to 1st week of 2010 = 5 weeks (important: begin… > end… always!). GetDateTimeDiff () will look like this in the constructor GetDateTimeDiff(“WeeksIso”, 2010, 0, 0, 1, 0, 00, 2009, 0, 0, 50, 0, 00).

Note: Year hast to be set always and begin year will always be the current year.

The GetDateTimeDiff() is defined like

        public string GetDateTimeDiff(
            string level,
            int beginYear,
            int beginQuarter,
            int beginMonth,
            int beginWeek,
            int beginDay,
            int beginHour,
            int endYear,
            int endQuarter,
            int endMonth,
            int endWeek,
            int endDay,
            int endHour
            )
        {
            if (level != string.Empty)
            {
                const int range = 100;
                const int YearRange = 10;
                int countHours = 0;
                int countDays = 0;
                int countWeeks = 0;
                int countMonth = 0;
                int countYear = 0;
                switch (level)
                {
                    #region Hour
                    case "Hours":
                        {
                            if (beginHour < 24 && endHour < 24)
                            {
                                DateTime _beginDate = new DateTime(beginYear, beginMonth, beginDay, beginHour, 0, 0);
                                DateTime _endDate = new DateTime(endYear, endMonth, endDay, endHour, 0, 0);
                                if (_beginDate.Hour > DateTime.Now.Hour && _beginDate > DateTime.Now)
                                    return "Begin Day and Hour must be set to current ";
                                if (_endDate == _beginDate)
                                    return "This Hour";
                                if (_endDate == _beginDate)
                                    return "Last Hour";
                                if (_endDate < _beginDate)
                                {
                                    countDays = GetHoursBetweenDates(_beginDate, _endDate);
                                    if (countHours > range)
                                        return "You can only review last 100 Hours";
                                    return GetHoursBetweenDates(_beginDate, _endDate) + " Hours Ago";
                                }
                            }
                            break;
                        }
                    #endregion

                    #region Days
                    case "Days":
                        {
                            DateTime _beginDate = new DateTime(beginYear, beginMonth, beginDay);
                            DateTime _endDate = new DateTime(endYear, endMonth, endDay);

                            if (_endDate > _beginDate)
                                return "Begin Day must be set to current day";
                            if (_endDate == _beginDate)
                                return "Today";
                            if (_endDate.Day == _beginDate.Day - 1)
                                return "Yesterday";
                            if (_endDate < _beginDate)
                            {
                                countDays = GetDaysBetweenDates(_beginDate, _endDate);
                                if (countDays > range)
                                    return "You can only review last 100 days";
                                return countDays + " Days Ago";
                            }
                            break;
                        }
                    #endregion

                    #region WeeksIso
                    case "WeeksIso":
                        {
                            if ( beginWeek <= WeeksInYearIso(beginYear) && endWeek <= WeeksInYearIso(endYear))
                            {
                                countWeeks = GetIsoWeekNumber(GetIsoWeekStartDate(beginYear, beginWeek), GetIsoWeekStartDate(endYear, endWeek));
                                if (countWeeks <= range)
                                    return countWeeks + " Weeks Ago";
                                return "You can only review Last 100 Weeks";
                            }
                            break;
                        }
                    #endregion

                    #region WeeksUs
                    case "WeeksUs":
                        {
                            if (beginWeek <= WeeksInYearUs(beginYear) && endWeek <= WeeksInYearUs(endYear))
                            {
                                if (beginYear < endYear)
                                    return "Begin Year cannot be greater that End Year";

                                if (beginYear == endYear)
                                {
                                    countWeeks = GetUsWeekNumber(GetUsWeekStartDate(beginYear, beginWeek)) - 
                                        GetUsWeekNumber(GetUsWeekStartDate(beginYear, endWeek));

                                    if (countWeeks <= range)
                                        return countWeeks + " Weeks Ago";
                                }
                                if (endYear < beginYear)
                                {
                                    countWeeks = GetUsWeekNumber(GetUsWeekStartDate(beginYear, beginWeek)) +
                                            WeeksInYearUs(endYear) - GetUsWeekNumber(GetUsWeekStartDate(endYear, endWeek));

                                    if (countWeeks <= range)
                                        return countWeeks + " Weeks Ago";
                                    return "You can only review Last 100 Weeks";
                                }
                            }
                            break;
                        }
                    #endregion

                    #region Months
                    case "Months":
                        {
                            DateTime _beginDate = new DateTime(beginYear, beginMonth, 1);
                            DateTime _endDate = new DateTime(endYear, endMonth, 1);

                            if (_beginDate > _endDate)
                            {
                                IEnumerator enumerator = GetMonth(_beginDate, _endDate).GetEnumerator();
                                while (enumerator.MoveNext())
                                {
                                    countMonth++;

                                    if (countMonth >= range)
                                        return string.Format("You can only review Last 100 Months\n {0} Months Ago", countMonth);
                                    if (_beginDate.Month - 1 == _endDate.Month &&
                                        _beginDate.Year == _endDate.Year)
                                        return "Last Month";
                                }
                                return countMonth + " Months Ago";
                            }

                            break;
                        }
                    #endregion

                    #region Quarter
                    case "Quarter":
                        {
                            if (beginQuarter == (int)GetQuarter(DateTime.Now.Month, DateTime.Now.Year) && beginYear == DateTime.Now.Year)
                            {
                                if (beginYear == endYear)
                                {
                                    int quarterCount = beginQuarter - endQuarter;

                                    return quarterCount + " Quarters Ago";
                                }
                                if (endYear < beginYear)
                                {
                                    int quarterCount = (4 - endQuarter) + beginQuarter;

                                    if (quarterCount > 4)
                                        return "You can only review upto last 4 Quarters";
                                    return quarterCount + " Quarters Ago";
                                }
                            }
                            return "Begin Quarter and Begin Year must be current";
                        }
                    #endregion

                    #region year
                    case "Years":

                        DateTime _beginYear = new DateTime(beginYear, 1, 1);
                        DateTime _endYear = new DateTime(endYear,1,1);
                        if (_endYear.Year > _beginYear.Year)
                            return "End Year must less than Begin year";
                        if (_beginYear.Year != DateTime.Now.Year)
                            return "Begin Year must be current year";
                        if (_endYear.Year < _beginYear.Year)
                        {
                            IEnumerator enumerator = GetYear(_beginYear, _endYear).GetEnumerator();
                            while (enumerator.MoveNext())
                            {
                                countYear++;
                                if (countYear >= YearRange)
                                    return "You can only review Last 5 years";
                            }
                            return countYear + " Years Ago";
                        }
                        if (_beginYear.Year == endYear)
                            return "This Year";
                        if (_beginYear.Year -1 == endYear)
                            return "Last Year";
                        break;
                    #endregion

                    default:
                        break;
                }
            }
            return string.Empty;
        }

Get days between dates
 
        private int GetDaysBetweenDates(
            DateTime beginDate,
            DateTime endDate)
        {
            return beginDate.Subtract(endDate).Days;
        }

        #endregion

Get hours between dates
 
        private int GetHoursBetweenDates(
            DateTime beginDate,
            DateTime endDate)
        {
            TimeSpan timeSpan = beginDate.Subtract(endDate);
            return (int)timeSpan.TotalHours;
        }

Get ISO week start date
 
        static DateTime GetIsoWeekStartDate(int year,int weekNumber)
        {
            DateTime januaryFirst = new DateTime(year, 1, 1);

            int daysOffset = DayOfWeek.Monday - januaryFirst.DayOfWeek;
            DateTime firstMonday = januaryFirst.AddDays(daysOffset);

            GregorianCalendar calendar = new GregorianCalendar(GregorianCalendarTypes.Localized);
            int firstWeek = calendar.GetWeekOfYear(januaryFirst, CalendarWeekRule.FirstFourDayWeek, DayOfWeek.Monday);

            if (firstWeek <= 1)
                weekNumber -= 1;

            DateTime weekStartDate = firstMonday.AddDays(weekNumber * 7);

            return weekStartDate;
        }

Get ISO weeks number
        public static int GetIsoWeekNumber(
            DateTime beginDate,
            DateTime endDate)
        {
            int Days = 0;
            int WeekCount = 0;

            TimeSpan Span = beginDate.Subtract(endDate);

            if (Span.Days <= 7)
            {
                if (endDate.DayOfWeek > beginDate.DayOfWeek)
                    return 1;
                return 0;
            }
            int x = (int)endDate.DayOfWeek;
            Days = Span.Days - (4 + (int)endDate.DayOfWeek);
            WeekCount = (Days / 7) + 2;

            return WeekCount;
        }

Get ISO weeks in year
        public static int WeeksInYearIso(
            int year)
        {
            GregorianCalendar cal =
                new GregorianCalendar(GregorianCalendarTypes.Localized);
            return cal.GetWeekOfYear(
                new DateTime(year, 12, 28), CalendarWeekRule.FirstFourDayWeek, DayOfWeek.Monday);
        }

Get US week start date
        static DateTime GetUsWeekStartDate(
            int year,
            int weekNumber)
        {
            DateTime januaryFirst = new DateTime(year, 1, 1);

            if (weekNumber < = 1)
                return januaryFirst;

            int daysOffset = DayOfWeek.Sunday - januaryFirst.DayOfWeek;
            DateTime Sunday = januaryFirst.AddDays(daysOffset);

            GregorianCalendar calendar = new GregorianCalendar(GregorianCalendarTypes.Localized);
            int week = calendar.GetWeekOfYear(januaryFirst, CalendarWeekRule.FirstDay, DayOfWeek.Sunday);

            if (week <= 1)
                weekNumber -= 1;

            DateTime weekStartDate = Sunday.AddDays(weekNumber * 7);

            return weekStartDate;
        }

Get US weeks number
        public static int GetUsWeekNumber(
            DateTime dateTime)
        {
            int weekNumber = 0;
            CultureInfo culture;

            culture = CultureInfo.CurrentCulture;
            weekNumber =
                culture.Calendar.GetWeekOfYear(dateTime, CalendarWeekRule.FirstDay, DayOfWeek.Sunday);

            return weekNumber;
        } 

Get US weeks in year
 
        public static int WeeksInYearUs(
            int year)
        {
            GregorianCalendar cal =
                new GregorianCalendar(GregorianCalendarTypes.Localized);
            return cal.GetWeekOfYear(
                new DateTime(year, 12, 31), CalendarWeekRule.FirstDay, DayOfWeek.Sunday);
        }

Get month(s)
 
        public IEnumerable<DateTime> GetMonth(
                DateTime beginMonth,
                DateTime endMonth)
        {
            for (DateTime month = endMonth; month < beginMonth; )
            {
                yield return month;
                month = month.AddMonths(1);
            }
        }

Get quarter(s)
 
        public static Quarter GetQuarter(int month,int year)
        {
            if (month <= (int)Month.March)
                return Quarter.First;
            else if ((month >= (int)Month.April) && (month <= (int)Month.June))
                return Quarter.Second;
            else if ((month >= (int)Month.July) && (month <= (int)Month.September))
                return Quarter.Third;
            else
                return Quarter.Fourth;
        }

Get year(s)
 
        public IEnumerable<DateTime> GetYear(
                DateTime beginYear,
                DateTime endYear)
        {
            for (DateTime yearDiff = endYear; yearDiff < beginYear; yearDiff = yearDiff.AddYears(1))
            {
                yield return yearDiff;
            }
        }

Month and Quarter are enums.
        
 public enum Month
        {
            January = 1,
            February = 2,
            March = 3,
            April = 4,
            May = 5,
            June = 6,
            July = 7,
            August = 8,
            September = 9,
            October = 10,
            November = 11,
            December = 12
        }
        public enum Quarter
        {
            First = 1,
            Second = 2,
            Third = 3,
            Fourth = 4
        }

In the constructor
            _txtBlock.Text += (GetDateTimeDiff("Hours", 2010, 0, 1, 0, 21, 00, 2010, 0, 1, 0, 20, 00) + "\n\n");
            _txtBlock.Text += (GetDateTimeDiff("Days", 2010, 0, 1, 0, 21, 00, 2010, 0, 1, 0, 01, 00) + "\n\n");
            _txtBlock.Text += (GetDateTimeDiff("WeeksIso", 2010, 0, 0, 1, 0, 00, 2009, 0, 0, 50, 0, 00) + "\n\n");
            _txtBlock.Text += (GetDateTimeDiff("WeeksUs", 2010, 0, 0, 1, 0, 00, 2009, 0, 0, 50, 0, 00) + "\n\n");
            _txtBlock.Text += (GetDateTimeDiff("Months", 2010, 0, 09, 0, 0, 00, 2007, 0, 08, 0, 0, 00) + "\n\n");
            _txtBlock.Text += (GetDateTimeDiff("Quarter", 2010, 1, 0, 0, 0, 00, 2008, 3, 0, 0, 0, 00) + "\n\n");
            _txtBlock.Text += (GetDateTimeDiff("Years", 2010, 0, 0, 0, 0, 00, 2005, 0, 0, 0, 0, 00) + "\n");

Download the source code here
Tested it here

Posted in Microsoft .Net, WPF | Tagged: | 1 Comment »

ALL ABOUT ORACLE PARTITIONING

Posted by austinslik on February 24, 2010

ALL ABOUT ORACLE PARTITIONING

The following lists the advantages of table partitioning:

1. Partitioning enables data management operations such as data loads, index creation and rebuilding and backup/recovery at the partition level rather than on the entire table. This results in a reduced time for these operations.
2. Partitioning improves query performance. In many cases, the results of a query can be achieved by accessing a subset of partitions, rather than the entire table. For some queries, this technique (called partition pruning) can provide order-of-magnitude gains in performance.
3. Partitioning can significantly reduce the impact of scheduled downtime for maintenance operations. Partition independence for partition maintenance operations lets you perform concurrent maintenance operations on different partitions of the same table or index. You can also run concurrent SELECT and DML operations against partitions that are unaffected by maintenance operations.
4. Partitioning increases the availability of mission-critical database if critical tables and indexes are divided into partitions to reduce the maintenance windows, recovery time and impact failures.
5. Partitioning can be implemented without requiring any modifications to your applications. For example, you could convert a nonpartitioned table to partitioned tables without needing to modify any of the SELECT statements or DML statements which access that table. You do not need to rewrite your application code to take advantage of partitioning.

Partition Key:
Each row in a partitioned table is unambiguously assigned to a single partition. The partition key is a set of one or more columns that determines the partition for each row.

• Consists of an ordered list of 1 to 16 columns
• Cannot contain a LEVEL, ROWID, or MLSLABEL pseudocolumn or a column of type ROWID
• Can contain columns that are NULLable
Partitioned Tables:

Tables can be partitioned into up to 64,000 separate partitions.
Any table can be partitioned except those tables containing columns with LONG and LON RAW.

Partitioning Method:

Based on our requirement for Data archiving and data lifecycle management along with ease of administration, Range Partitioning will be the Best option.
But otherwise, there are the following partitioning methods available:
List Partitioning, Hash Partitioning, Composite Partitioning

List, Range, and Hash Partitioning

Range partitioning maps the data based on ranges of partition key values that you establish for each partition. It is the most common type of partitioning and is often used with dates.

The following lists the step by step procedure to create a Range Partitioned Table and the indexes on it.

1. Create a partitioned test table PURCHASE_DOCUMENT_PARTITIONED from PURCHASE_DOCUMENT

To test the partitioning we will use the following syntax to create a new partitioned table:

create table EC1.PURCHASE_DOCUMENT_PARTITIONED
(
  ID                       NUMBER(11) not null,
  INVOICE_NO               VARCHAR2(50),
  PURCHASE_ID              NUMBER(11) not null,
  PURCHASEDOCUMENTTYPE_ID2 CHAR(7),
  DATA                     BLOB,
  SECRET                   VARCHAR2(20 CHAR),
  REIMBURSEMENT_ID         NUMBER(11),
  DOCUMENT_DATE            TIMESTAMP(6) not null,
  CTIME                    TIMESTAMP(6) default systimestamp not null,
  MTIME                    TIMESTAMP(6) default systimestamp not null,
  MINFO                    VARCHAR2(4000),
  MUSERACCOUNT_ID          NUMBER(15),
  CUSERACCOUNT_ID          NUMBER(15),
  IS_SIGNED                NUMBER(1) default 0 not null,
  DUE_DATE                 TIMESTAMP(6),
  PURCHASEDOCUMENTTYPE_ID  CHAR(3) not null,
  PAYMENTDELINQUENCY_LEVEL NUMBER(2) default 0 not null,
  VAT_ID                   VARCHAR2(30))
PARTITION BY RANGE (CTIME)
(
partition y2005 values less than (to_date('01.01.2006','DD.MM.YYYY') ) tablespace cb_data_slow
PCTUSED 99 PCTFREE 1 STORAGE(INITIAL 100M NEXT 5M minextents 1 maxextents unlimited),
partition y2006 values less than (to_date('01.01.2007','DD.MM.YYYY') ) tablespace cb_data_slow
PCTUSED 99 PCTFREE 1 STORAGE(INITIAL 100M NEXT 5M minextents 1 maxextents unlimited),
partition y2007 values less than (to_date('01.01.2008','DD.MM.YYYY') ) tablespace cb_data_slow
PCTUSED 99 PCTFREE 1 STORAGE(INITIAL 100M NEXT 5M minextents 1 maxextents unlimited),
partition y2008q1 values less than (to_date('01.04.2008','DD.MM.YYYY') ) tablespace cb_data_slow
PCTUSED 99 PCTFREE 1 STORAGE(INITIAL 100M NEXT 5M minextents 1 maxextents unlimited),
partition y2010 values less than (MAXVALUE)
tablespace cb_data_med
PCTUSED 40 PCTFREE 10 STORAGE(INITIAL 100 M NEXT 2M minextents 1 maxextents unlimited)
)

2. Create dummy1, dummy2, dummy3

We have so far decided upon creating 3 partitions. Hence 3 dummy tables need to be created.

Create table ec1.dummy1 as select * from ec1.purchase_document 
where ctime &gt;= to_date('01.01.2005','DD.MM.YYYY') and ctime = to_date('01.01.2006','DD.MM.YYYY') and ctime = to_date('01.01.2007','DD.MM.YYYY') and ctime = to_date('01.06.2009','DD.MM.YYYY') and ctime = to_date('01.06.2009','DD.MM.YYYY') and ctime &lt;= to_date('05.06.2009','DD.MM.YYYY')

4. Creating primary key partitioned indexes.

Primary key partitioned index could be Local or Global.
Step 1: First create a unique index. We will try to create a local index.

CREATE UNIQUE INDEX EC1.PURCHASE_DOC_PK_IDX ON EC1.PURCHASE_DOCUMENT_PARTITIONED (ctime)
     LOCAL
     (partition Y2005,
      partition Y2006,
      partition Y2007,
      partition Y2008Q1,
      partition Y2010)
      TABLESPACE CB_DATA_SLOW
      pctfree 10
      initrans 2
      maxtrans 255
      storage
      (
       initial 64K
        minextents 1
         maxextents unlimited
      );

But we decide not to use ‘CTIME’ as a primary key but ‘ID’ as the primary key.

Drop Index EC1.PURCHASE_DOC_PK_IDX;

Rerun the local index creation with the index on ID.

CREATE UNIQUE INDEX EC1.PURCHASE_DOC_PK_IDX ON EC1.PURCHASE_DOCUMENT_PARTITIONED (ID)
     LOCAL
     (partition Y2005,
      partition Y2006,
      partition Y2007,
      partition Y2008Q1,
      partition Y2010)
      TABLESPACE CB_DATA_SLOW
      pctfree 10
      initrans 2
      maxtrans 255
      storage
      (
       initial 64K
        minextents 1
         maxextents unlimited
      );

ERROR:

ORA-14039: partitioning columns must form a subset of key columns of a UNIQUE Index
Hence we shall create either Global Partitioned Unique Index or Global Non Partitioned Unique Index in such a column as ID. Here we are using non partitioned Global Index. Global Partitioned index cannot be created on a non prefixed column.

CREATE UNIQUE INDEX EC1.PURCHASE_DOC_PK_IDX 
 ON EC1.PURCHASE_DOCUMENT_PARTITIONED (ID)
 GLOBAL
TABLESPACE CB_DATA_SLOW
pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    minextents 1
    maxextents unlimited
  );

Step 2: Second Create the Primary key on the same column. The Primary key will utilize the underlying index.
ALTER TABLE EC1.PURCHASE_DOCUMENT_PARTITIONED ADD CONSTRAINT PURCHASE_DOC_PARTITIONED_PK
PRIMARY KEY (ID);

5. Create the Remaining Unique Indexes.

 CREATE UNIQUE INDEX EC1.PURCHASE_DOCUMENT_UK_INDX 
 ON EC1.PURCHASE_DOCUMENT_PARTITIONED(INVOICE_NO, SECRET)
 GLOBAL
 TABLESPACE CB_DATA_SLOW
 pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    minextents 1
    maxextents unlimited
  );
  
ALTER TABLE EC1.PURCHASE_DOCUMENT_PARTITIONED ADD CONSTRAINT PURCHASE_DOC_PARTITIONED_UK
UNIQUE (INVOICE_NO, SECRET);

 CREATE UNIQUE INDEX EC1.PURCHASE_DOC_INVOICENO_UK_INDX 
 ON EC1.PURCHASE_DOCUMENT_PARTITIONED(INVOICE_NO, PURCHASEDOCUMENTTYPE_ID)
 GLOBAL
 TABLESPACE CB_DATA_SLOW
 pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    minextents 1
    maxextents unlimited
  );
  
ALTER TABLE EC1.PURCHASE_DOCUMENT_PARTITIONED ADD CONSTRAINT PURCHASE_DOC_PART_INVOICENO_UK
UNIQUE (INVOICE_NO, PURCHASEDOCUMENTTYPE_ID);

6. Create the Foreign Keys and Checks.

alter table EC1.PURCHASE_DOCUMENT_PARTITIONED
  add constraint PURCHASE_DOC_PART_FK_PURCHASE foreign key (PURCHASE_ID)
  references EC1.PURCHASE (ID);
alter table EC1.PURCHASE_DOCUMENT_PARTITIONED
  add constraint PURCHASE_DOC_PART_FK_TYPE foreign key (PURCHASEDOCUMENTTYPE_ID)
  references EC1.PURCHASEDOCUMENTTYPE (ID);
alter table EC1.PURCHASE_DOCUMENT_PARTITIONED
  add constraint PUR_DOC_FK_REIMBURSEMENT foreign key (REIMBURSEMENT_ID)
  references EC1.REIMBURSEMENT (ID);
-- Create/Recreate check constraints 
alter table EC1.PURCHASE_DOCUMENT_PARTITIONED
  add constraint INVOICE_NO_DOCTYP_CHECK
  check (&quot;INVOICE_NO&quot; IS NOT NULL AND &quot;PURCHASEDOCUMENTTYPE_ID&quot; in ('PDN', 'RFP', 'CHB', 'INV', 'REF', 'REC', 'OCO', 'QUO', 'TST', 'TSR') OR &quot;INVOICE_NO&quot; IS NULL AND &quot;PURCHASEDOCUMENTTYPE_ID&quot; NOT in ('PDN', 'RFP', 'CHB', 'INV', 'REF', 'REC', 'OCO', 'QUO', 'TST', 'TSR'));

7. Build Local Partitioned Index on the ctime column which also happens to be a partitioned key.
CREATE INDEX EC1.PURCHASE_DOC_PARTITIONED_CTIME  ON EC1.PURCHASE_DOCUMENT_PARTITIONED (ctime)
     LOCAL
     (partition Y2005,
      partition Y2006,
      partition Y2007,
      partition Y2008Q1,
      partition Y2010)
      TABLESPACE CB_DATA_SLOW
      pctfree 10
      initrans 2
      maxtrans 255
      storage
      (
       initial 64K
        minextents 1
         maxextents unlimited
      );

The syntax for creating global partitioned Index (although we don’t use it) is as followes:
CREATE INDEX EC1.PURCHASE_DOC_PARTITIONED_CTIME  ON EC1.PURCHASE_DOCUMENT_PARTITIONED (ctime)
     GLOBAL 
     PARTITION BY RANGE (CTIME)
(
partition y2005 values less than (to_date('01.01.2006','DD.MM.YYYY') ) tablespace cb_data_slow
STORAGE(INITIAL 100M NEXT 5M minextents 1 maxextents unlimited),
partition y2006 values less than (to_date('01.01.2007','DD.MM.YYYY') ) tablespace cb_data_slow
STORAGE(INITIAL 100M NEXT 5M minextents 1 maxextents unlimited),
partition y2007 values less than (to_date('01.01.2008','DD.MM.YYYY') ) tablespace cb_data_slow
STORAGE(INITIAL 100M NEXT 5M minextents 1 maxextents unlimited),
partition y2008q1 values less than (to_date('01.04.2008','DD.MM.YYYY') ) tablespace cb_data_slow
STORAGE(INITIAL 100M NEXT 5M minextents 1 maxextents unlimited),
partition y2010 values less than (MAXVALUE)
tablespace cb_data_med
STORAGE(INITIAL 100 M NEXT 2M minextents 1 maxextents unlimited)
)

8. Build local index on the PURCHASE_ID and PURCHASEDOCUMENTTYPE_ID columns. (Non Prefixed)
create index EC1.PURCHASE_DOC_PART_PURCHASE on EC1.PURCHASE_DOCUMENT_PARTITIONED (PURCHASE_ID, PURCHASEDOCUMENTTYPE_ID)
  LOCAL
     (partition Y2005,
      partition Y2006,
      partition Y2007,
      partition Y2008Q1,
      partition Y2010)
  TABLESPACE CB_DATA_SLOW
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    minextents 1
    maxextents unlimited
  );

Alternatively Build Global Non Partitioned Index.
create index EC1.PURCHASE_DOC_PART_PURCHASE on EC1.PURCHASE_DOCUMENT_PARTITIONED (PURCHASE_ID, PURCHASEDOCUMENTTYPE_ID)
  GLOBAL
    TABLESPACE CB_DATA_SLOW
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    minextents 1
    maxextents unlimited
  );

OR Global Partitioned Index (Prefixed)
create index EC1.PURCHASE_DOC_PARTITIONED_DATE on EC1.PURCHASE_DOCUMENT_PARTITIONED (DOCUMENT_DATE)
GLOBAL
PARTITION BY RANGE (DOCUMENT_DATE)
(
partition y2005 values less than (to_date('01.01.2006','DD.MM.YYYY') ) tablespace cb_data_slow
STORAGE(INITIAL 100M NEXT 5M minextents 1 maxextents unlimited),
partition y2006 values less than (to_date('01.01.2007','DD.MM.YYYY') ) tablespace cb_data_slow
STORAGE(INITIAL 100M NEXT 5M minextents 1 maxextents unlimited),
partition y2007 values less than (to_date('01.01.2008','DD.MM.YYYY') ) tablespace cb_data_slow
STORAGE(INITIAL 100M NEXT 5M minextents 1 maxextents unlimited),
partition y2008q1 values less than (to_date('01.04.2008','DD.MM.YYYY') ) tablespace cb_data_slow
STORAGE(INITIAL 100M NEXT 5M minextents 1 maxextents unlimited),
partition y2010 values less than (MAXVALUE)
tablespace cb_data_med
STORAGE(INITIAL 100 M NEXT 2M minextents 1 maxextents unlimited)
);

NOTE: Remember that the column to be indexed and the partition key of the index should be same in case of Global Partitioned Index.
OR Local Partititioned Index (local non prefixed indexes)
create index EC1.PURCHASE_DOC_PARTITIONED_DATE on EC1.PURCHASE_DOCUMENT_PARTITIONED (DOCUMENT_DATE)
  LOCAL
     (partition Y2005,
      partition Y2006,
      partition Y2007,
      partition Y2008Q1,
      partition Y2010)
      TABLESPACE CB_DATA_SLOW
      pctfree 10
      initrans 2
      maxtrans 255
      storage
      (
       initial 64K
        minextents 1
         maxextents unlimited
      );

create index EC1.PURCHASE_DOC__PART_RP on EC1.PURCHASE_DOCUMENT_PARTITIONED (REIMBURSEMENT_ID, PURCHASEDOCUMENTTYPE_ID)
  LOCAL
     (partition Y2005,
      partition Y2006,
      partition Y2007,
      partition Y2008Q1,
      partition Y2010)
      TABLESPACE CB_DATA_SLOW
      pctfree 10
      initrans 2
      maxtrans 255
      storage
      (
       initial 64K
        minextents 1
         maxextents unlimited
      );

Few Points to remember:
Local Index could be prefixed or non prefixed.
Global index cannot be non prefixed and always prefixed.
Creation of Unique Local Index on a different column other than partitioned column is not possible. In those cases Global Indexes need to be used.
Use Local Indexes for Data Warehouse and DSS systems. Use Global Indexes for the OLTP system. Its easy to do maintenance on the local indexes than Global, as any changes to the partition structure, Global Indexes need be rebuilt.

9. Rename partition.

Alter table ec1.purchase_document_partitioned rename partition y2010 to maxvalue1;

10. Split Partition

11. Move the partition to different tablespace.

 Alter table EC1.PURCHASE_DOCUMENT_PARTITIONED
  Move Partition Y2006
  tablespace CB_DATA_SLOW

This invalidates the indexes. All global indexes (partitioned and non partitioned) are invalidated along with the partitioned column whose data was moved.
Run :
  Alter index EC1.PURCHASE_DOC_PARTITIONED_DATE 
  rebuild partition Y2010 online

Run alter table reduild index index_name online; for all non partitioned indexes on a partitioned tables.

12. Drop the partition. Check how the indexes are getting affected.
Dropping partition also invalidates the global indexes.

13. Run Gather Stats.

NOTE: Understanding PCTFREE and PCTUSED .

In a create table statement there is a clause for pctfree and pctused which are the block usage parameters. PCTUSED is a parameter which makes a block eligible for insert or update. Like the default setting for PCTUSED is 40%, the moment the block utilization drops to below 40% the block is relinked to the freelist. This implies that the block is eligible for further inserts or update until it reaches PCTFREE value. Lets say PCTFREE is 10%, The block continues to accept values until only 10% remain. This 10% is reserved for further growth of the block due to updates.

In a datawarehouse scenario where the block willl not be updated at all, the PCTFREE could be 0 and PCTUSED could be 100.

Some Glossary of SQLs:

Select * from dba_tab_partitions
where table_owner='EC1' 
     
Select * from dba_part_tables
where owner='EC1'

Select * from dba_indexes where table_name='PURCHASE_DOCUMENT_PARTITIONED'

Select * from ec1.purchase_document_partitioned 
partition (Y2008q1)
where ID=2064811

SELECT *
FROM dba_ind_partitions
where index_owner='EC1'
 ORDER BY index_name, partition_name;

Select * from dba_part_indexes where owner='EC1';

Select * from dba_tables where table_name='PURCHASE_DOCUMENT'
select * from dba_indexes where table_name='PURCHASE_DOCUMENT_PARTITIONED'

select table_name, partition_name, TABLESPACE_NAME, INI_TRANS, MAX_TRANS, 
  PCT_FREE, PCT_USED, INI_TRANS, INITIAL_EXTENT, NEXT_EXTENT, MAX_EXTENT, 
  PCT_INCREASE, FREELISTS, FREELIST_GROUPS, LOGGING, BUFFER_POOL 
  from dba_tab_partitions
  where table_name='PURCHASE_DOCUMENT_PARTITIONED'  

  Select * from dba_constraints where table_name='PURCHASE_DOCUMENT_PARTITIONED'
  
  Select * from dba_indexes where table_name='PURCHASE_DOCUMENT_PARTITIONED'

Posted in Uncategorized | Tagged: | 1 Comment »

Sort or delete ListBox Item Wpf

Posted by austinslik on February 23, 2010

This example will show a simple way to delete or sort listbox selectedItem using WPF C#.
Let’s start with creating xaml mark-up that includes a button, a combobox and a listbox. It should look like this

<Window x:Class="CommandBinding.Window1"
xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation"
xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"
xmlns:sys="clr-namespace:System;assembly=mscorLib"
xmlns:local="clr-namespace:CommandBinding"
Title="Window1" Height="308" Width="194"
WindowStartupLocation="CenterScreen">
<Grid>
<Grid.RowDefinitions>
<RowDefinition Height="20"/>
<RowDefinition Height="*" />
<RowDefinition Height="20" />
</Grid.RowDefinitions>
<StackPanel Orientation="Horizontal" Grid.Row="0">
<ComboBox Width="100" x:Name="_comboBox" SelectedIndex="0" SelectionChanged="_comboBox_SortBySelection" >
<sys:String>Id</sys:String>
<sys:String>FirstName</sys:String>
<sys:String>LastName</sys:String>
</ComboBox>
</StackPanel>
<ListBox x:Name="_listBox" Grid.Row="1">
</ListBox>
</Grid>
</Window>

Notice that the combobox has

<sys:String>Id</sys:String>
<sys:String>FirstName</sys:String>
<sys:String>LastName</sys:String>

This is because it’s a small sample. It can also be done by binding the combobox to an enum.
The

SelectionChanged="_comboBox_SortBySelection"

will be explained shortly. So let’s move on to code behind.
Here we need some data to work with so we’ll start by creating a person object that looks like this

class Person
{
    private readonly int _id;
    private readonly string _firstName;
    private readonly string _lastName;
    public Person(int id, string firstName, string lastName)
    {
        _id = id;
        _firstName = firstName;
        _lastName = lastName;
    }

    public int Id { get{return _id; }}
    public string FirstNam { get{return _firstName; }}
    public string LastName { get{return _lastName;}}
}

And a collection class that will inherit observable collection to collect our person object we’ve just created. It looks like this

class PersonsCollection:ObservableCollection<Person>
{
  public PersonsCollection()
  {
      Add(new Person(5, "A", "B"));
      Add(new Person(3, "A", "B"));
      Add(new Person(2, "A", "B"));
      Add(new Person(6, "A", "B"));
      Add(new Person(1, "A", "B"));
      Add(new Person(4, "A", "B"));
  }
}

You can fill the “A”,”B” with any name of your choice.
Notice the observable collection has the person class in its greater and less than bracket<Person> which in original definition ObservableCollection<T> The T means generic which means we can throw any object in there and it will hold it and allow add,delete, etc more about observableCollection.

So now that we have some data we need to bind it to our listbox i.e. we need to modify our xaml mark-up a little but first we need supply the listboxItemsource with some data in the constructor(this can also done in xaml but i prefer c#) like this

_listBox.ItemsSource = new PersonsCollection();

and in the listbox marp-up would look like this

<ListBox x:Name="_listBox" Grid.Row="1">
    <ListBox.ItemTemplate>
        <DataTemplate>
            <StackPanel Orientation="Horizontal" >
                <TextBlock Text="{Binding Path=Id}" xml:space="preserve"/>
                <TextBlock Text="{Binding Path=FirstName}" xml:space="preserve"/>
                <TextBlock Text="{Binding Path=LastName}" />
            </StackPanel>
        </DataTemplate>
    </ListBox.ItemTemplate>
</ListBox>

Binding is beyond the scope of this tutorial. At this stage if you hit key Ctrl+F5 you should get

Listbox with value to sort

Now we see our data but can’t sort or delete. Let’s start with sorting by initializing a new instance of the System.Window.Input.RoutedUICommand class which takes some information as parameter e.g.
public static RoutedUICommand = new RoutedUICommand(descriptive text for the command,the declared name of the command for serialization, type that is registering the command (which is the class where is’s being instantiated)).

The code will look like this

public static RoutedUICommand SortPerson = new RoutedUICommand("SortPerson","SortPerson", typeof(Window1));

then in the constructor we’ll call on a property commandbindings from a CommadbingsCollection initialized in UIElement class. It is a collection that gets all the CommandBinding objects (that can be executed) associated with the element SortPerson we created. The code looks like this:

CommandBindings.Add(System.Windows.Input.CommandBinding(SortPerson, SortEventEventArgs)).

Notice SortEventEventArgs in the bracket; this is an ExecutedRoutedEventArgs function that is associated with SortPerson. So let’s go ahead and create it

private void SortEventEventArgs(object sender, ExecutedRoutedEventArgs e)
{
ListCollectionView view =
(ListCollectionView)CollectionViewSource.GetDefaultView
(_listBox.ItemsSource);
view.SortDescriptions.Clear();
view.SortDescriptions.Add(
new SortDescription(e.Parameter.ToString(),ListSortDirection.Ascending));
}

Notice the ListCollectionView. Brief explanation: it provides us the features like Navigation, Sorting, Filtering and Grouping. You can see that the listboxItemSource is added to the listcollectionview… that will supply the listcollectionview with data so that we can navigate, sort, filter or group them. Now the last thing we need to do for sorting is to send a command to listcollectionview with a sort criteria to sort with and that can be done by adding a SelectionChanged to combobox and in the event function we will create the command. In xaml mark-up of combobox we will add something like this SelectionChanged=”_comboBox_SortBySelection” which we’ve already done above. Now in the code behind we’ll create an event function that looks like this

private void _comboBox_SortBySelection(object sender, SelectionChangedEventArgs e)
{
    ICommand comand = SortPerson;
    comand.Execute(e.AddedItems[0]);
}

Inside the this function we created a command by creating an instance of ICommand Class and initialized it with the element SortPerson which is an instance of RoutedUICommand. Now we can use its Execute method to pass a sort criteria as a parameter. If an item is selected then the combobox selectionChanged will trigger this command (Comman.Execute(e.AddedItems[0])) to pass the newly selected item to listcollectionview as string to sort with that coloumn. At this point if you hit key Ctrl+F5 you would be able to sort the listbox items.

Now that we can sort the listbox let’s start with delete. Here we don’t have to do much, first create the same kind of RoutedUICommand like the one we created earlier like this

public static RoutedUICommand DeletePerson =
    new RoutedUICommand("DeletePerson", "DeletePerson", typeof(Window1))

and create an event function just like we did above like this

private void DeleteEventArgs(object sender, ExecutedRoutedEventArgs e)
{
Person selectedItem = (Person)e.Parameter;
dataSource.Remove(selectedItem);
}

Now what we are doing here in the function is just casting the received object back to person object (to the type datasource recognises ) so we can remove it from our datasource, don’t forget our data source is the class PersonCollection. We need to add the above function to Commandbindings like we deed with sorting like this

CommandBindings.Add(new System.Windows.Input.CommandBinding(DeletePerson, DeleteEventArgs, CanExecuteEventHandler))

the third parameter “CanExecuteEventHandler” will be explained shortly. Next we will go and modify our xaml a little so the button we created will do something when its clicked and we can achieve by binding (believe me in wpf anything can be bind). In the button mark-up add

Command="local:Window1.DeletePerson" CommandParameter="{Binding ElementName=_listBox, Path=SelectedItem}"

where Window1 is the name of the project class. The button mark-up would look like this

<Button Grid.Row="2" Content="Delete" x:Name="_deleteButton"
            Command="local:Window1.DeletePerson"
            CommandParameter="{Binding ElementName=_listBox, Path=SelectedItem}"/>

To be able to bind the RoutedIUCommand instance we create in the code behind to Command, we have to import the projects

namespace like this xmlns:local="clr-namespace:CommandBinding"

and CommandParameter is to specify the element the command is going to work with and the path(what to notice in the given element ) to selected item. the listbox and its selected item. That’s all to it. Now we can also delete.

Since this Wpf gave us some nice features like CanExecuteRoutedEventArgs which helps to decide if a command can be executed(it’s a Boolean property that returns true or false) lets add it to the delete button so that its only enable when you click on the listboxitem you would like to delete and after that it disables automatically unless an item is selected. So, lets create a function called CanExecuteEventHandler (used as a parameter in commandbindings above) like this


private void CanExecuteEventHandler(object sender, CanExecuteRoutedEventArgs e)
{
    if ((e.CanExecute = e.Parameter != null) && (e.Parameter is Person))
    e.Handled = true;
}

The if statement checks if something is been selected and the selected item is persons object, (that means the command associated to this element can execute) then the handle which if true will enable the element its associated to else false which means the associated element will remain disabled.

That’s all to it.

Full Source code here

Posted in Microsoft .Net, WPF | Tagged: | Leave a Comment »

very interesting concept advertising

Posted by austinslik on February 4, 2010

In the world of internet advertising and tv promotions, print media remains one of the competitive and evolving form of advertisement. Check out some of the best advertisements of recent times.

Bose : Noise reduction headphones

Belgian Association for Obese Patients

Laser Cycle Helmets

Timotei : Lion

Pringles: Hot and Spicy

Guiness: Einstein

German Olympic Sports Federation

Utopolis Group Of Cinemas

Chupa Chups: Sugar Free

Fibermesh: Concrete reinforcement solution

Watch this space for more conceptual advertisements. We will be glad to get a comment from you.

Share it with your friends.

Posted in Contemporary | Tagged: | 2 Comments »

Futuristic Guitar

Posted by austinslik on February 2, 2010

Did you enjoy watching the video? Let us know about our contents by leaving a comment below.

add to del.icio.usDigg it Stumble It! post to facebookRetweet

Posted in Gadgets | Tagged: | Leave a Comment »

Experience the Future of Technology!

Posted by austinslik on February 1, 2010

Did you enjoy watching the video? Let us know about our contents by leaving a feedback below.

Posted in Gadgets | Tagged: | Leave a Comment »

MDX previous and current day/month

Posted by austinslik on July 22, 2009

I is a newby in MDX :) my job is to write an MDX query to compare revenues e.g. Today to Yesterday, This Week to Last Week , This Month to Last Month. To cut the whole story short, i cunstructed this code below and it works perfectly:

The function to get today’s sales: PERIODSTODATE and to get yesterday’s sales: PARALLELPERIOD. the same functions i used for Month too.(the code formating isn’t perfect )

Current day/month

WITH

MEMBER  [Measures].[Day Revenue] AS
AGGREGATE(

PERIODSTODATE([Calendar].[Day],[Calendar].CurrentMember),
[Measures].[Sales Revenue EUR]
)

MEMBER  [Measures].[Month Revenue] AS
AGGREGATE(

PERIODSTODATE([Calendar].[Month],  [Calendar].CurrentMember),
[Measures].[Sales Revenue EUR]
)

Previous day/month

MEMBER  [Measures].[Yesterday Revenue] AS

PARALLELPERIOD([Calendar].[Day],1,[Calendar].CurrentMember),
[Measures].[Day Revenue]
)
MEMBER [Measures].[Last  Month Revenue] AS
PARALLELPERIOD([Calendar].[Month],1,[Calendar].CurrentMember),
[Measures].[Month Revenue]
)

This is just to make my select statement compact

MEMBER  [Measures].[Today] AS
([Measures].[Day Revenue], ([Calendar].[Hour]))

MEMBER  [Measures].[Yesterday] AS
([Measures].[Yesterday Revenue], ([Calendar].[Hour]))

MEMBER  [Measures].[This Month] AS
([Measures].[Month Sales Revenue], ([Calendar].[Hour]))

MEMBER  [Measures].[Last Month] AS
([Measures].[Month Revenue], ([Calendar].[Hour]))

SELECT
{    [Measures].[Today],
[Measures].[Yesterday],
[Measures].[This Month],
[Measures].[Last Month]
}ON COLUMNS
FROM  [Cube]

Date formating/converting(string) problem? go to Liyasker site

If you have a suggestion or a better idea on how to make this code more efficient, comment.

Posted in Microsoft .Net, SQL Server analysis (MDX) | Tagged: | 2 Comments »

 
Follow

Get every new post delivered to your Inbox.