MDX: Запрос для вывода статистики с нарастающим итогом

Допустим есть куб "Регистрации", в котором лежат реги на неком сервисе по дням. Т.е. в кубе есть мера [Measures].[Accounts] и измерение [DimRegDate] с иерархией [DimRegDate].[Year-Month-Day]. Необходимо вывести за некий промежуток дат общее количество рег на этот день и количество новых рег вэтот день.

Для этого нужно создать два мембера - один с общим количеством рег за все время и второй с суммой рег за дни, которые идут после текущего дня. Потом из первого вычесть второй.

Запрос будет выглядеть вот так:

WITH MEMBER [Measures].[TotalAccounts_ALL] AS SUM([Measures].[Accounts], [DimRegDate].[Year-Month-Day].[All])
MEMBER [Measures].[TotalAccounts_After] AS SUM([DimRegDate].[Year-Month-Day].nextmember:null, [Measures].[Accounts])
MEMBER [Measures].[TotalAccounts] AS [Measures].[TotalAccounts_ALL] - [Measures].[TotalAccounts_After]

SELECT { [Measures].[TotalAccounts], [Measures].[Accounts]}  ON 0,
{ [DimRegDate].[Year-Month-Day].[CreatedYear].&[2011].&[9].&[19]:[DimRegDate].[Year-Month-Day].[CreatedYear].&[2011].&[9].&[25] } ON 1
FROM [Регистрации]

ASP.NET, логин через Facebook и ошибка e.root is undefined

Сегодня начал делать логин через Facebook на ASP.NET. Ну в общем тут и не важна серверная технология, т.к. все можно сделать на стороне клиента. Описание, как все делать, есть тут: http://developers.facebook.com/docs/guides/web/#login

Вроде бы все просто. Добавляем скрипт:

<script src="http://connect.facebook.net/en_US/all.js"></script>

Добавляем инициализацию:

       <script>
         FB.init({
            appId:'YOUR_APP_ID', cookie:true,
            status:true, xfbml:true
         });
      </script>

Добавляем специальный тег, который потом чудесным образом превратиться в красивую фейсбуковскую кнопку:

<fb:login-button>Login with Facebook</fb:login-button>

Запускаем и .. нифига не работает. Кнопка не появляется, а в яваскрипте возникает ошибка, какой то там root is undefined.

Погуглив, нашел решение, оказывается надо в код страницы еще добавить вот такой тег:

<div id="fb-root"></div>

Во всех примерах он есть, но вот в тексте он не упоминается и я его не заметил.

После его добавления все работает.

Собственно что делать дальше, читайте фейсбуковскую доку.

T-SQL: Поиск по джобам

Для поиска джоба можно применять следующий скрипт:

select j.job_id, j.name, s.step_name, j.description
from msdb.dbo.sysjobs j
inner join msdb.dbo.sysjobsteps s on j.job_id = s.job_id
where s.command like '%some_text%'

s.command - команда, которую выполняет шаг джоба;
j.name - название джоба;
s.step_name - название шага джоба;
j.description - описание джоба.

nolock при обращении к талице на линкованом сервере

Если обратиться к таблице на линкованом SQL-сервере с директивой nolock:

SELECT * FROM [192.168.0.1].Users.dbo.users (nolock)

то будет выдаваться ошибка:

Msg 4122, Level 16, State 1, Line 1
Remote table-valued function calls are not allowed.

Решается вот так:

SELECT * FROM [192.168.0.1].Users.dbo.users WITH (nolock)

Ответ нашел тут.

SSRS: Как рассылать отчеты по подписке НЕ по расписанию

Речь в статье пойдет о рассылке отчетов по подписке, но не по расписанию, а в те моменты, когда это необходимо. Например только в случае успешного процессинга OLAP-куба. Как раз такой случай я и буду рассматривать.

Все относиться к SQL Server 2008 R2 Enterprise Edition, т.к. от версии к версии база SSRS может меняться. Я делал подписку управляемую данными и не уверен, что все нижеследующее подойдет к обычной подписке, не проверял.

Когда в SSRS создается подписка, последней страницей задается расписание рассылки, но бывает так, что трудно угадать время, когда отчет уже можно формировать, например в какой то день может быть слишком много данных и они не успеют загрузиться. В моем случае бывало, что кубы процессились с ошибками, в итоге отчет приходил некоректный.

Так вот, при создании расписания рассылки отчета, SSRS создает на сервере, где лежит его база, джоб. Называться он будет как-нибудь вот так: 2D45C6DB-559E-43CB-A019-F8F1484E17FF и он будет запускаться по расписанию, которое было указано в SSRS.

В джобе вызывается процедура ReportServer.dbo.AddEvent, которая кладет ID подписки в таблицу ReportServer.dbo.Event, из этой таблицы SSRS понимает, что пора отчет отсылать, после отсылки запись из таблицы ReportServer.dbo.Event удаляется. 

Исходя из вышесказанного, задача сводиться к тому, что надо добавить запись с ID подписки в таблицу ReportServer.dbo.Event в тот момент, когда пора отсылать отчет.

Шаг 1 - узнаем ID отчета (все селекты идут в БД ReportServer):

select ItemID from catalog where name = @name

Шаг 2 - узнаем ID подписки:

select SubscriptionID from Subscriptions where report_OID =@ItemID

 Шаг 3 - записываем данные в таблицу Event:

exec ReportServer.dbo.AddEvent @EventType='TimedSubscription', @EventData=@SubscriptionID

В моем случае, в джобе, после шага, который процессит куб, я добавил шаг, который выполняет:

exec ReportServer.dbo.AddEvent @EventType='TimedSubscription', @EventData='fa502959-73cf-59a1-b018-8ddcdae2e01'

Надо иметь в виду, что если отчет удалить, а потом выложить заново, все ID поменяются . Если отчет выложить,  перезаписав его, то все ID останутся прежними.

И еще есть важный момент - надо сделать так, что бы расписание самого отчета не срабатывало, для чего в интерфейсе SSRS на странице создания расписания надо поставить Однократно.

T-SQL: Как проверить выполнение джоба

Бывают случаи, когда необходимо сделать так, что в джобе некоторые шаги при ошибочном выполнении не приводят к завершению джоба с ошибкой, а переходят на другой шаг джоба. У меня таким образом работает куб, который процессит OLAP-кубы. Что бы не следить каждый день за выполнением джоба, мы настроили SMS-рассылку. Для этого потребовалось разработать механизм проверки работы джоба. Т.е. задача - найти шаги джоба, которые завершились ошибкой, но при этом джоб продолжил работу.

Для начала нужно получить ID джоба:

select job_id from msdb..sysjobs where name = @job_name

Лог работы джобов лежит в таблице msdb..sysjobhistory. Нужно вытащить записи, которые относятся к последнему выполнению джоба. У меня джоб выполняется каждый день, поэтому просто беру сегодняшнее число. Но в таблице msdb..sysjobhistory дата начала выполнения шага храниться не в datetime, а в двух полях типа int: run_date и run_time, т.е. дата 2011-08-11 13:54:33 будет лежать в виде run_date = 20110811 и run_time = 135433.

Наверное с такими датами можно было бы работать и в интовых значениях, но мне как то привычнее в datatime. Для преобразования такого формата в datetime я написал две функции:

 -- Конвертация времени из формата HHMMSS типа int в секунды
create function [dbo].[fn_convert_intdt_to_sec] ( @ts int )
    returns int
as
begin
    if (@ts < 60) return @ts
     
    declare @sec int = @ts - (@ts/100)*100
    declare @min int = (@ts - (@ts/10000)*10000)/100
    declare @hr int = @ts/10000
     
    return @hr*3600 + @min*60 + @sec
end

-- Конвертация времени из формата даты в джобе в нормальный DateTime
create function [dbo].[fn_convert_job_date_to_datetime]( @date int, @time int )
returns DateTime
as
begin
    return dateadd(second, dbo.fn_convert_intdt_to_sec(@time), convert(datetime, cast(@date as varchar), 112))
end

Далее выводим список шагов:

select instance_id, step_id, step_name, run_status, dbo.fn_convert_job_date_to_datetime(run_date, run_time) run_dt
from msdb..sysjobhistory
where job_id = @job_id
and step_id <> 0 and dbo.fn_convert_job_date_to_datetime(run_date, run_time) >= @dt
order by step_id

step_id = 0 - это строка для всего джоба, поэтомы ее исключаем.

Тут (http://msdn.microsoft.com/en-us/library/ms174997.aspx) лежит описание таблицы  msdb..sysjobhistory.Статусы у шага могут быть такие:

0 = Failed
1 = Succeeded
2 = Retry
3 = Canceled

 В проге, которая дергает данные, я проверяю, что у всех шагов статус = 1 , если нет, то шлю СМС с ошибкой.

C#: System.Diagnostics.Process - не срабатывает событие Exited

Для того, что бы срабатывало событие Exited экземпляра класса System.Diagnostics.Process нужно установить свойство EnableRaisingEvents в true. Т.е.:

process.EnableRaisingEvents = true;

process.Exited += new EventHandler(process_Exited);

Распространяется свойство только на событие Exited, т.е. если его не установить в true, то другие события, например OutputDataReceived, будут срабатывать. Описание тут: http://msdn.microsoft.com/en-us/library/system.diagnostics.process.enableraisingevents.aspx

C#: как запустить приложение из кода

Для того, что бы запустить приложение из кода, существует класс System.Diagnostics.Process.

Я запускал консольное приложение через WEB-страницу. То, что консольное приложение выводит в стандартный вывод надо было собирать в лог-файл. Весь код страницы приводить не буду, приведу только ту часть, которая запускает приложение и пишет лог:

private string path_log = "C:\\parser\\"; // Папка, где лежат логи парсера
private string path_ex = "C:\\parser\\log\\"; // Папка, где лежит экзешник парсера

private string file_name = "log.txt"; // Лог-файл

private System.IO.StreamWriter sw_log = null;
private System.Diagnostics.Process parser = null;

private void StartParser()
{
        parser = new Process();
        parser.StartInfo.FileName = path_ex + "parser.exe";
        parser.StartInfo.UseShellExecute = false;

        // Указываем, что будем хватать стандартный вывод
        parser.StartInfo.RedirectStandardOutput = true;

        // А так же кодировку, в которой данные выходят в стандартный вывод
        parser.StartInfo.StandardOutputEncoding = System.Text.Encoding.UTF8;
        parser.StartInfo.CreateNoWindow = true;
        parser.StartInfo.WorkingDirectory = path_ex;

        // ВАЖНОЕ СВОЙСТВО!! Без него не будет срабатывать событие parser.Exited
        parser.EnableRaisingEvents = true;

        // Событие, которое срабатывает, когда в стандартный вывод поступает инфа

        parser.OutputDataReceived += new DataReceivedEventHandler(parser_OutputDataReceived);
        parser.Exited += new EventHandler(parser_Exited);

        // ПУСК!
        parser.Start();

        sw_log = new System.IO.StreamWriter(path_log + "\\" + file_name + ".txt");
        // Говорим, что пора ловить данные из стандартного вывода
        parser.BeginOutputReadLine();
        // Возвращаю ID процесса      
        log_content.InnerHtml = string.Format("Parser process ID: {0}", parser.Id);
}

// Пишем стандартный вывод процесса в лог-файл. 

// Я сделал, что бы файл после каждой строчки флашился, мне просто так надо было, не принципиально

private void parser_OutputDataReceived(object sender, DataReceivedEventArgs e)
{
        if (!String.IsNullOrEmpty(e.Data))
        {
            sw_log.WriteLine(e.Data);
            sw_log.Flush();
        }
}

// Закрыаем все и освобождаем

private void parser_Exited(object sender, EventArgs e)
{
        parser.Close();
        parser.Dispose();

        sw_log.Close();
        sw_log.Dispose();
}

В общем все. Из возможно полезного в классе Process есть метод WaitForExit() - который будет ждать, когда запускаемый процесс завершит работу. Так же как стандартный вывод можно ловить инфу из вывода ошибки, для этого есть событие ErrorDataReceived и метод BeginErrorReadLine. Так же там много всякой отладочной инфы по процессу. Подробности тут: http://msdn.microsoft.com/en-us/library/system.diagnostics.process.aspx.

ASP.NET: Как файл PrecompiledApp.config влияет на Global.asax

Использую ASP.NET 2.0. При развертывании веб-приложения на сервере (IIS) удалил файл PrecompiledApp.config, т.к. посчитал его не важным. После чего долго удивлялся, почему в файле Global.asax.cs не выполняется метод Application_Start. 

Здесь обсуждается эта проблема: http://forums.asp.net/t/1694548.aspx/1/10

После того, как положил на сервер файл PrecompiledApp.config все заработало. Не знаю баг это или фича. Вообще, судя по описаниям, файл используется для отслеживания надо ли какие то файлы компилировать при очередном деплое сайта.

Ошибка MySQL "data too long" при вставке

При вставке данных в таблицу в поле с типом  blob произошла ошибка "data too long". У меня стоит MySQL 5.5.13 на Windows Server 2008 R2 Standard, винда английская. Погуглив, нашел массу сообщений об этой ошибке и несколько вариантов решения. Мне помогло вот это: http://forums.mysql.com/read.php?103,51906,103853#msg-103853 Надо в my.ini из строки

sql-mode=STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

убрать STRICT_TRANS_TABLES и перезапустить мускул.

C#: как получить символ по коду UTF-8

Мне нужно было из строки удалить символ, который имел код UTF-8 8232. Думал получить по коду переменную типа string и с помощью метода Replace заменить на пустую строку.

Что бы получить строку по коду, можно воспользоваться методом:

System.Text.Encoding.UTF8.GetString

Метод принимает массив byte[], соответственно код 8232 нельзя туда подсунуть, т.к. он больше максимального байта. Я подумал, может, если я разобью этот код на два байта, то все получиться, т.е. сделал так:

string str = System.Text.Encoding.UTF8.GetString(new byte[] { 0x20, 0x28 }, 0, 2);

Но в итоге, в переменной str оказывалось значение " (". Совсем не то, что нужно.

Погуглив нашел решение, надо взять не код в таблице символов, а строку, в которую кодируется этот символ в URL. В моем случае - это строка %E2%80%A8 и выполнить следующий метод:

string str = Uri.UnescapeDataString("%E2%80%A8");

Тогда в str окажется необходимый символ. И далее можно с ним работать строковыми методами.

Решение нашел тут: http://social.msdn.microsoft.com/forums/en-US/csharpgeneral/thread/9b97dbcc-0cd7-4b87-9bb2-0dc60f9890b3

T-SQL: Функция ROW_NUMBER

Недавно открыл для себя очень удобную в некоторых случаях функцию ROW_NUMBER. Появилась она в SQL Server 2005.

Ее можно использовать в селектах для нумирации строк, можно с помощью нее селектить только строки с такого-то по такой-то номер, а так же можно выдавать номер строки в некоторй группе строк. Примеры можно посмотреть в описании.

Мне больше всего нравиться последний случай ее применения. Т.к. раньше такого рода задачи приходилось решать через курсоры, что вело к большим блокам кода и тормозам при исполнении.

Т.е. допустим есть таблица:

CREATE TABLE user_items
(
    id int not null primary key,
    user_id int not null,
    item_id int not null,
    added_at datetime not null
)

И хочется, что бы для каждого пользователя в таблице осталось не более 5 записей, которые были добавлены последними. С использованием ROW_NUMBER это можно сдедать так:

delete u from user_items u
inner join
(
    select ROW_NUMBER() OVER (partition by user_id order by added_at desc) rn, user_id, item_id, id
    from user_items
) a on u.id = a.id
where a.rn > 5

 Т.е. во вложенном запросе выбираются все записи и проставляются номера записей для каждого пользователя в обратном порядке добавления. Соответственно записи, у которых номер больше 5, надо удалить.

Автоматическое партицирование OLAP-кубов в SSAS 2008

В статье Партицирование OLAP-кубов в SSAS 2008 я рассказывал зачем и как партицировать кубы в SSAS. Бывают случаи, когда партицировать надо часто и руками это делать скучно. Поэтому хочется этот процесс автоматизировать.

В моем случае мне нужно партицировать в трех кубах в общей сложности 10 групп мер. Если этого не делать каждую неделю, то время процессинга за две недели возрастает с 3,5 часов до 5 с лишним.

В статье Взаимодействие с OLAP кубами (SSAS 2008) на C# я рассказывал, как работать с кубами из кода. Сначала я начал решать задачу автоматического партицирования через разработку проги, но что то сильно углубился в программирование и мне эта идея перестала нравиться, поэтому я выбрал другой путь - создание пакета SQL Server Integration Services (SSIS). Здесь я и опишу этот способ.

Идея следующая - группы мер партицируются по годам плюс есть текущая партиция, которая процесситься каждый день. Т.е., например, для продаж есть партиции за 2010 год, 2011 и current. Раз в неделю для каждой группы мер необходимо изменять партицию за текущий год (2011) и партицию current, так что бы текущая партиция содержала данные только за несколько дней. Потом процессить партицию за текущий год, что бы не было пробелов в данных. После нового года надо будет руками создавать новую годовую партицию и менять процедуру изменения партиций.

Начальное партицирование куба надо провести руками, автоматически все с нуля не создается.

Для примера возьмем, что партиции группы мер продаж формируются следующими SQL-запросами:

2010: SELECT id, sale_date, user_id, money_sum, product_id FROM sales WHERE sale_date >= '2010-01-01' AND sale_date < '2011-01-01'
2011: SELECT id, sale_date, user_id, money_sum, product_id FROM sales WHERE sale_date >= '2011-01-01' AND sale_date < '2011-05-31'
current: SELECT id, sale_date, user_id, money_sum, product_id FROM sales WHERE sale_date >= '2010-05-31'

Эти запросы и надо будет менять в партициях за 2011 год и в текущей партиции.

Для начала необходимо сгенерить скрипты XMLA для изменения партиций. Я буду делать все на примере только одной группы мер. Пакет SSIS будет сделан так, что количество не будет иметь значения.

Cоздаем папку: C:\XMLAScripts. Далее, для генерации скрипта в Management Studio открываем список партиций нужной группы мер, на текущей партиции тыкаем правой кнопкой, а далее как на картинке:

В последнем меню выбираем File... и сохраняем в созданной папке под названием sales_current.xmla. Далее тоже самое повторяем для партиции текущего года и сохраняем в другой файл sales_2011.xmla.

Далее открываем файлы по-очереди и видим что-то похожее на это:

<Alter ObjectExpansion="ExpandFull" xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
    <Object>
        <DatabaseID>cubeDbSales</DatabaseID>
        <CubeID>cubeSales</CubeID>
        <MeasureGroupID>Vw Sales 1</MeasureGroupID>
        <PartitionID>Vw Sales 1</PartitionID>
    </Object>
    <ObjectDefinition>
        <Partition ...>
            <ID>Vw Sales 1</ID>
            <Name>Vw Sales</Name>
            <Source xsi:type="QueryBinding">
                <DataSourceID>dbOLAP</DataSourceID>
                <QueryDefinition>SELECT id, sale_date, user_id, money_sum, product_id FROM sales WHERE sale_date &gt;= '2010-05-31'</QueryDefinition>
            </Source>
            <StorageMode>Molap</StorageMode>
            <ProcessingMode>Regular</ProcessingMode>
            <ProactiveCaching>
                <SilenceInterval>-PT1S</SilenceInterval>
                <Latency>-PT1S</Latency>
                <SilenceOverrideInterval>-PT1S</SilenceOverrideInterval>
                <ForceRebuildInterval>-PT1S</ForceRebuildInterval>
                <Source xsi:type="ProactiveCachingInheritedBinding" />
            </ProactiveCaching>
        </Partition>
    </ObjectDefinition>
</Alter>

В каждом файле заменяем дату, которая разделяет партицию текущего года и текущую партицию, в данном примере это 2011-05-31, на какую-нибудь последовательность символов. Я поменял на "###dt###", т.е. запрос в скрипте будет выглядеть вот так:

 <QueryDefinition>SELECT id, sale_date, user_id, money_sum, product_id FROM sales WHERE sale_date &gt;= '###dt###'</QueryDefinition>

Далее эта последовательность символов будет меняться на нужную дату.

В BIDS создаем Integration Services Project. Из Toolbox'а перетаскиваем Foreach Loop Container, а в него Script Task и Analysis Services Execute DDL Task. В итоге должно получиться вот так:

Далее создаем переменные:

Розовые уголки в иконках - это у меня плагин стоит, который я описываю в статье Как поменять область видимости переменной в BIDS (SSIS).

Для переменных dt и dt_str устанавливаем свойство EvaluateAsExpression = True. Для dt свойство Expression: GETDATE(). Для dt_str свойство Expression:

(DT_WSTR, 4)(YEAR(@[User::dt]))+"-"+
(LEN((DT_WSTR, 2)(MONTH(@[User::dt])))==1 ? "0"+(DT_WSTR, 2)(MONTH(@[User::dt])) : (DT_WSTR, 2)(MONTH(@[User::dt])))+"-"+
(LEN((DT_WSTR, 2)(DAY(@[User::dt])))==1 ? "0"+(DT_WSTR, 2)(DAY(@[User::dt])) : (DT_WSTR, 2)(DAY(@[User::dt])))

Такое выражение формирует из даты строку формата yyyy-MM-dd, другого пути я не нашел.

В совойствах Foreach контенера устанавливаем:

1. В разделе Collection:
1. Enumerator = Foreach File Enumerator
2. Folder = C:\XMLAScripts
3. Files = *.xmla 
2.  В разделе Variable Mappings: Variable=User:file_name, Index = 0

 В свойствах блока Script в разделе Script:

1. ReadOnlyVariables = User::dt_str,User::file_name
2. ReadWriteVariables = User::alter_code

В коде скрипта пишем (у меня SQL Server 2008, поэтому пишу на C#):

        public void Main()
        {
            string file_name = Dts.Variables["file_name"].Value.ToString();
            StreamReader sr = new StreamReader(file_name);

            string content = sr.ReadToEnd();

            sr.Close();
            sr.Dispose();

            Dts.Variables["alter_code"].Value = content.Replace("###dt###",  Dts.Variables["dt_str"].Value.ToString());

            Dts.TaskResult = (int)ScriptResults.Success;
        }

 В свойствах блока Alter Cube в разделе DDL:

1. Connection -> создаем новое соединение с кубом
2. SourceType =Variable
3. Source = User::alter_code

На этом создание SSIS пакета закончено. Foreach контейнер выбирает из папки C:\XMLAScripts все файлы с расширением xmla, в блоке Script текущий файл читается, в нем производиться замена строки "###dt###" на сегодняшнюю дату в формате yyyy-MM-dd, а блок Alter Cube передает полученную таким образом команду OLAP-серверу. Для того, что бы добавить другие группы мер для партицирования, надо в папку C:\XMLAScripts добавить XMLA-файлы с альтерами для этих групп, в которых дата в SQL-запросе будет заменена на "###dt###".

Далее нужно создать джоб, в котором первым шагом сделать бэкап куба на всякий случай, вторым шагом запустить этот SSIS пакет, а далее отпроцессить все партиции за текущий год.

Партицирование OLAP-кубов в SSAS 2008

В целях повышения производительности работы кубов в SSAS предусмотрена возможность разбиения групп мер на партиции. Вот так это выглядит в Management Studio:

При процессинге куба процессится только партиция Vw Sales By Products, две другие уже не процессятся, т.к. данные за эти периоды не меняются. При больших объемах данных партицирование может значительно уменьшить время процессинга, а так же уменьшит время обращения к кубу, особенно если партиции разнести по разным физическим носителям.

Создать партиции можно как в Management Studio, так и в BIDS. Рекомендую делать партиции в BIDS, т.к. если их сделать в Management Studio, т.е. на рабочем кубе, а не в проекте, то при следующем деплое куба все партиции удаляться.

Для создания партиций в BIDS необходимо в дизайнере куба открыть вкладку Partitions:

Здесь видно две группы мер Sales и Sales 1 и список их партиций. Для того, что бы разбить на партиции группу мер Sales, необходимо сначала изменить текущую партицию. Разбивать на партиции будем по дате продажи. Для этого в колонке Source для партиции жмем кнопку, которая появляется при получении этим полем фокуса ввода. Появиться окно:

 

 По умолчанию Binding type = Table Binding. Его надо изменить на Query Binding,  в окне появиться поле с SQL-запросом, который используется для обращения к базе при процессинге куба, с пустым условием WHERE. В условие надо добавить выражение для партицирования. Я хочу разбивать на партиции по дате продажи, текущая партиция будет процесситься каждый день, в условие я добавляю:

WHERE sale_date >= '2011-05-31'

Жмем OK. Текущая партиция создана. Далее необходимо создать партицию (или несколько партиций) для исторических данных. Предположим продажи есть только за 2011 год. Для создания партиции жмем ссылку New Partition. Появиться окно:

 Выбираем таблицу в поле Available tables. Появиться окно, где вводиться запрос, в нем чекаем галку Specify a query to restrict rows и в запросе пишем условие ограничения:

WHERE sale_date >= '2011-01-01' AND sale_date < '2011-05-31'

 Далее нажимаем Next, появиться окно где можно выбрать опцию Processing loaction (не знаю, что это, не разбирался) и Storage location (т.е. где будут физически лежать файлы для этой партиции). Далее нажимеам Next, появиться окно, где в поле Name вбиваем название партиции, например Vw Sales 2011, выбираем Design aggregations later и жмем Finish.

Все, после этого окно партиций примет такой вид:

 Далее деплоим и процессим куб. Далее, в повседневной работе куба,    процесим только партицию Vw Sales, т.к. данные будут обновляться только в ней.

Перевод строки в выражениях SSRS

Для добавления новых строк в выражениях SSRS используется константа vbCrLf. Например, вот так я добавлял тултип для графика:

="MIN: " + Format(Min(Fields!Сумма_покупок.Value), "#,##0") + vbCrLf + "MAX: " +   Format(Max(Fields!Сумма_покупок.Value), "#,##0")

Конвертация даты в строку в SSRS 2008

Для того, что бы преобразовать дату в строку в произвольном формате в SQL Server Reporting Services, надо воспользоваться функцией Format:

=Format(Parameters!par_date.Value, "yyyy-MM-dd")

Так же можно написать так:

=Parameters!par_date.Value.ToString()

или

=FormatDateTime(Parameters!par_date.Value, DateFormat.ShortDate)

но в обоих последних примерах нельзя выбрать произвольный формат даты, в ToString вообще нельзя указать формат, а в FormatDateTime можно выбрать из нескольких вариантов.

SSAS 2008 R2: ошибка при включении сервиса

Стоит SQL Server 2008 R2 Enterprise Edition на Windows Server 2008 R2 Standard.
При включении сервиса SQL Server Analysis Services возникает ошибка. В логах винды пишет такое:

The file '\\?\C:\DATA\CryptKey.bin' could not be opened. Please check the file for permissions or see if other applications locked it.

Сервис запускался из под пользователя NETWORK SERVICE. По какой-то причине на папку C:\Data у этого пользоватля не было. Для решения проблемы надо дать пользователю, из под которого запускается сервис, полный доступ на папку C:\Data и на все файлы и вложенные папки в этой папке.

Разбиение отчета в SSRS на страницы через параметр отчета

При выводе отчета в Web SSRS автоматически разбивает его на страницы, но есть возможность управлять этим процессом через параметр отчета. Все нижеследующее относиться к SQL Server 2008 R2. Я раньше делал это на SQL Server 2005 таким же способом, но какие то детали могут отличаться.

Для того, что бы разбить отчет на страницы через параметр, надо создать параметр типа Int, в моем случае он будет означать количество строк на станице. Далее создаем группу и в значение группировки вставляем выражение:

=Ceiling(RowNumber(Nothing)/Parameters!rows_per_page.Value)

В таблицу добавиться поле, что бы оно не мешалось, я делаю его ширину нулевой.

Далее, в свойствах группы, в разделе Page Breaks ставим галку Between each instance of a group. После этого я запустил отчет и он мне выдал ошибку, суть которой в том, что у таблицы выражение сортировки использует функцию RowNumber, а такого не может быть. После некоторых попыток избавиться от этого пришел к такому решению - для таблицы и для группы (на всякий случай) установил значения сортировки на первом попавшемся поле, а потом сбросил эти значения. Видимо при создании группы, выражение группировки автоматом попало в выражение сортировки (Мелкомягким привет!). 

Далее на кнопке строки отчета, которая появляется слева от самой таблицы при попадании таблицы в фокус ввода, тыкаем правой кнопкой и выбираем пункт Tablix Properties. На вкладке General нужно поставить галку Keep together on one page if possible, иначе могут появиться странности, например у меня при значении 50 строк на страницу выдавало каждые несколько страниц по 3-4 записи. Видимо автоматическое разбиение на страницы создается  без учета других разбиений.

Единственное чего я не смог сделать, так это оставить названия колонок на каждой странице. В окне Tablix Properties есть галка Repeat header rows on each page, но что-то ее установка ни к чему не привела.

К стати, здесь про разбиение на странице кратко написано: http://msdn.microsoft.com/en-us/library/ms157328.aspx , раздел Page Breaks.

Значок "замка" на папке или файле в Windows Server 2008 R2

Иногда в Windows Server 2008 R2, а так же в Windows 7, можно увидеть в эксплорере значок "замка" на папке или файле. Это знак того, что к папке (файлу) ограничен доступ пользователей, т.е. можо разрешить доступ, например, только себе. С одной стороны штука полезная, но с другой, может и напрячь, особенно если о ней не знать.

Я хотел к SQL Server приаттачить базу, которая лежала в папке с таким ограничением и SQL Server просто не видел файл внутри папки. Решение оказалось простым, надо было дать доступ к папке и самому файлу пользователю, из под которого был запущен SQL Server. В моем случае NETWORK SERVICE.

Как так получилось, что на папке оказался "замок", точно не знаю, я деаттачил базу, потом хотел расшарить папку для того, что бы перелить файл на другой сервер. Потом шару снял и появился замок и обратно приаттачить базу не вышло :))

Проверка правильности JSON

Вот здесь: http://json.org/ внизу страницы перечислены различные утилиты для работы с JSON. Мне понравилась утилита проверки правильности JSON-кода написанная на C#: JSON_checker.

Кстати, о том, зачем я начал проверять JSON. Сначала я использовал в проекте www.asvix.ru jQuery 1.3.2 и когда я делал вызов метода:

$.getJSON(url, callback);

То callback-функция нормально вызывалась после получения ответа с сервера. У меня с сервера возвращался не очень корректный ответ в виде JSON, примерно такой: {x:"50",y:"100"}.

После перехода на jQuery 1.5 колбэки перестали работать, т.е. сама callback-функция работала, если ее вызвать на прямую, но из getJSON она не вызывалась. Проблема оказалась в том, что JSON был некорректен. Исправил на сервере, что бы возвращало правильно: {"x":"50","y":"100"} и все заработало.

Редирект 301 (Moved Permanently) в IIS 7

Поисковые машины считают, что сайты http://site.ru и http://www.site.ru являются разными сайтами. Поэтому, если оба этих URL ведут на один и тот же контент, поисковая машина может понижать ваш сайт в результатах выдачи за дублирущийся контент. Чтобы такого не произошло, нужно в файле robots.txt указать строку:

Host: www.site.ru

В которой указывается основное доменное имя (все равно какое выбрать с www или без него, дело вкуса). А со второго имени нужно сделать редирект с кодом 301 на основное доменное имя.

В IIS 7 редирект можно сделать несколькими способами, правда не все подходят для этого случая.

1. Самый простой способ сделать редирект - это выделить в IIS в папке Sites необходимый домен и в основной панели два раза тыкнуть HTTP Redirect. В открывшемся окне установить галку Redirect requests to this destination и указать необходимый URL, ниже в выпадающем списке выбрать код редиректа 301 и в меню справа тыкнуть Apply. Подробнее и с картинками можно почитать тут: http://www.trainsignaltraining.com/windows-server-2008-http-redirection .

Этот способ для рассматриваемого случая не подходит, т.к. он предполагает наличие двух разных сайтов на одном сервере или редирект с одного сервера на другой.

2. Второй способ: установить дополнительный модуль для ASP.NET URL Rewrite Module 2.0, а далее прописать правила редиректа в Web.Config:

<configuration>
  <system.webServer>
    <rewrite>
      <rules>
        <rule name="Redirect to WWW" stopProcessing="true">
          <match url=".*" />
          <conditions>
            <add input="{HTTP_HOST}" pattern="^site.ru$" />
          </conditions>
          <action type="Redirect" url="http://www.site.ru/{R:0}" redirectType="Permanent" />
        </rule>
      </rules>
    </rewrite>
  </system.webServer>
</configuration>

Код взят из статьи SEO Canonical URLs And 301 Redirects In Windows IIS 6, IIS 7.

3. Третий способ в Global.asax добавить метод  Application_BeginRequest со следующим содержанием:

    void Application_BeginRequest(object sender, EventArgs e)
    {
        string req_domain = HttpContext.Current.Request.Url.ToString().ToLower();

        if (req_domain.Contains("http://site.ru"))
        {
            req_domain = req_domain.Replace("http://site.ru", "http://www.site.ru");
           
            Response.Clear();
            Response.StatusCode = 301;
            Response.AddHeader("Location", req_domain);
            Response.End();
        }
    }

Взаимодействие с OLAP кубами (SSAS 2008) на C#

Работу с данными рассматривать не буду, потому что сам еще не пробовал.

Для того, что бы работать с кубами из кода необходимо скачать библиотеку ADOMD.NET. Забирать надо ADOMD.NET.msi. Установить ее. Далее, в VS добавить ее в проект (на иконке проекта тыкаем правой кнопкой и выбираем Add Reference...). Я работал на VS 2010 и после установки библиотеки я ее не нашел на вкладке .NET окна Add Reference, поэтому пошел на вкладку Browse и добавил ее руками. Выбирать надо вот этот файл C:\Program Files\Microsoft.NET\Adomd.NET\80\Microsoft.AnalysisServices.AdomdClient.dll.

Классы, необходимые для соединения к кубам и отправки им команд, находятся в пространстве имен Microsoft.AnalysisServices.AdomdClient.

Соединиться с БД (имеется в виду БД кубов) и выполнить команду можно следующим образом:

            AdomdConnection cnn = null;

            try
            {
                cnn = new AdomdConnection("Provider=MSOLAP;  DataSource=server_ip_or_name;Initial Catalog=db_name");
                cnn.Open();

                AdomdCommand cmd = cnn.CreateCommand();
                cmd.CommandType = System.Data.CommandType.Text;
                cmd.CommandText = command;

                cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                 // Обработка ошибки
            }
            finally
            {
                cnn.Close();
            }

Как я писал выше, я не пробовал еще работать с данными кубов из кода, но таким кодом точно можно отпроцессить куб, разбить меру на партиции и т.п. Соответствующая команда в формате XMLA подставляется вместо переменной command. Опять же, я пока не разбирался можно ли туда передать MDX и как работать с ответом.

Небольшой хинт: для того, что б написать команду к кубу для какой-либо операции, не обязательно напрягать голову, т.к. в Management Studio можно сгенерить скрипт команды. Например, что бы сгенерить команду для процессинга куба необходимо на кубе тыкнуть правой кнопкой, далее Porcess и в открывшемся окне нажать кнопку Script, она будет вверху. Скрипт сгенериться в новое окно запроса. Так же можно сгенерить скрипт для команды Merge для партиций меры. Так же, для элементов куба можно сгенерить команды CREATE, ALTER и DELETE. У кубов, партиций и измерений есть пункт в меню Script cube as, Script partition as, Script dimension as.

PostgreSQL: как посмотреть список таблиц

В PostgreSQL список таблиц можно посмотреть запросом:

select * from information_schema.tables

Список колонок:

select * from information_schema.columns

В общем, так же как и в MS SQL Server. 

Как указать IIS 7 какой IP слушать

Если у вас стоит IIS 7, у вас есть несколько IP, то IIS будет слушать их все на 80 порту. Если необходимо установить еще один веб-сервер на один из IP на 80 порт, нужно указать IIS 7, какой IP слушать ему. Делается это с помощью утилиты Netsh, которая входит в поставку Windows:

netsh http add iplisten ipaddress=xxx.xxx.xxx.xxx

Решение нашел тут: http://serverfault.com/questions/148439/how-can-i-control-which-ip-address-iis7-uses

Ухаживаем за индексами в SQL Server

Периодически индексы надо ребилдить или реорганизовывать. Выбор, что применять, делается по проценту фрагментации индекса.

Посмотреть процент фрагментации индексов в базе данных можно с помощью запроса:

use DB_NAME 


SELECT ps.database_id, ps.OBJECT_ID, o.name table_name,
ps.index_id, b.name,
ps.avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS ps
INNER JOIN sys.indexes AS b ON ps.OBJECT_ID = b.OBJECT_ID
INNER JOIN sys.objects o ON b.OBJECT_ID = o.OBJECT_ID
AND ps.index_id = b.index_id
WHERE ps.database_id = DB_ID()
ORDER BY ps.avg_fragmentation_in_percent desc

 Если фрагментация больше 30%, то ребилдим:

ALTER INDEX ALL ON table_name REBUILD

Эта команда ребилдит все индексы в таблице. Вместо ALL можно поставить имя индекса, тогда ребилдиться будет только он.

Если фрагментация 5%-30%, то реорганизуем:

ALTER INDEX ALL ON table_name REORGANIZE

Естественно, проценты не жесткие, все зависит от ситуации и собственного мнения DBA.

В первоисточнике можно почитать тут: http://msdn.microsoft.com/en-us/library/ms189858.aspx

Очистка лога с помощью SHRINKDATABASE в SQL Server 2008

В SQL Server 2008 используется несколько моделей восстановления (Recovery Models) для баз данных:

1. Simple - в логе минимум информации, можно восстанавливаться из полного или инкрементального бэкапа, но нельзя восстановиться в состояние на определенный момент времени;
2. Full - можно восстановиться в состояние на определенный момент времени;
3. Bulk-logged - в этом типе операции с большими массивами данных (Bulk) логируются в минимальном размере, в отличии от Full recovery model.

Подробнее можно почитать тут:  http://databases.about.com/od/sqlserver/a/recoverymodels.htm 

К чему перечисление моделей восстановления? К тому, что по умолчанию SQL Server ставит Recovery Model = Full, что приводит к тому что команда SHRINKDATABASE не приводит к очистке лога.

Для того, что бы очистить лог, сначала надо сменить Recovery Model:

alter database db_name set recovery simple

Посмотреть какая у баз стоит Recovery model можно так:

select name,recovery_model_desc from sys.databases

Далее можно очищать лог:

DBCC SHRINKDATABASE (db_name)

 Есть другой способ очистить лог, если не хочется отключать Full recovery model. Нужно сделать Detach базы, удалить файл лога и снова приатачить базу, тогда создаться пустой лог-файл.

Как запускать зашифрованный SSIS пакет из джоба

При создании SSIS пакета в BIDS в свойствах есть опция ProtectionLevel со следующими возможными значениями:

1. DontSaveSensitive - не сохранять в пакете паролей (имеются в виду пароли от соединений к БД и другим источникам и получателям данных);
2. EncryptSensitiveWithUserKey - зашифровывать пароли с помощью ключа пользователя, который основан на профайле пользователя)
3. EncryptSensitiveWithPassword - зашифровывать пароли с помощью пароля, который вводится в свойствах в поле PackagePassword;
4. EncryptAllWithPassword - шифровать все введенным паролем;
5. EncryptAllWithUserKey - шифровать все с помощью ключа пользователя;
6. ServerStorage - сохранить пакет в базе msdb и защитить его с помощью ролей базы данных.

По умолчанию стоит EncryptSensitveWithUserKey.

Подробнее можно почитать тут: http://msdn.microsoft.com/en-us/library/ms141747.aspx

Последний пункт ни когда не использовал, т.к. предпочитаю запускать пакеты прямо из файлов, мне так удобнее. Но видимо, если пойти путем сохранения пакета в БД, то проблема пароля снимается.

А проблема состоит в том, что при использовании UserKey для шифрования SQL Server Agent должен быть запущен из под того же пользователя, что и делал сам пакет. Когда на сервере работает один пользователь проблем с этим не возникает. 

В моей практике был случай, когда я все делал из под доменного пользователя, у меня было порядка 20 пакетов, в которых стояло EncryptSensitiveWithUserKey. В один день упал контроллер домена, всех пользователей на домене админы заводили заново, имена всем дали те же, но профайлы естественно были другими. Пришлось все пакеты открывать и заново вводить все пароли к БД и FTP-соединениям. Даже страшно себе представить, что бы было, если бы в пакетах была выбрана опция EncryptAllWithUserKey...

На новом проекте пакеты делаются одним пользователем, а SQL Server Agent запущен из под другого и поменять это никак нельзя. Поэтому я стал использовать опцию EncryptSensitiveWithPassword. 

Для того, что бы запустить зашифрованный паролем пакет из джоба необходимо сделать следующее:

1. В редакторе шага джоба выбрать type = SQL Server Integration Services Package;
2. Package source = File system;
3. Package - указать путь к файлу;
4. На вкладке Command line (при ее открытии редактор попросит ввести пароль, который был использован для шифрования в пакете) выбрать Edit the command line manually. Для редактирования откроется поле Command line и там после опции /DECRYPT надо ввести пароль, т.е. /DECRYPT password.
5. Нажать OK для сохранения шага.

Подробнее об опциях командной строки при запуске SSIS-пакетов можно почитать тут: http://msdn.microsoft.com/en-us/library/ms162810.aspx .

Как изменить значение Identity у таблицы в MS SQL Server

Для того что бы задать новое значение поля identity в таблице надо выполнить следующую функцию:

DBCC CHECKIDENT (<table_name>, reseed, <новое_значение>)

Миграция данных из PostgreSQL в MS SQL Server

Миграция данных из PostgreSQL 8.3 в SQL Server 2008 R2 Standard  Edition.

Сначала я попытался прилинковать PostgreSQL к SQL Server, используя оригинальный драйвер ODBC. Когда создавал соединение, все было хорошо, т.е. Test Connection проходил, но при попытке линка вываливалась ошибка. Я не стал особо копать, т.к. знаю, что ODBC - один из самых глючных способов линковки к SQL Server. Проблемы возникают даже когда к одному SQL Server линкуется другой SQL Server.

Далее я начал искать другие дрова. Первыми нашел вот это http://pgfoundry.org/frs/?group_id=1000085&release_id=539 , но не стал качать, т.к. смутила дата последнего релиза  - 17.04.2006.

Далее на сайте PostgreSQL я нашел список различных дров: http://www.postgresql.org/download/products/2. По описанию мне понравились вот эти PGNP, я пробовал, работает отлично, порадовало так же поддержка SSIS, но из-за платности, я от них отказался.

Далее мой выбор пал на продукт, под названием Npqsql, по сути это библиотека для .Net, бесплатная, на ней я и остановился, но для миграции данных потребовалось написать код на C#:

using System;
using System.Collections.Generic;
using System.Text;

using System.Configuration;
using System.IO;

using Npgsql;

namespace Zapuskatr
{
    class Program
    {
        static string COLS_DELIMITER = "|";
        static string ROW_DELIMITER = "\n";
        static string CNN_STRING = null;
        static string QUERY = null;
        static string PATH = null;

        static void Main(string[] args)
        {
            if (CNN_STRING == null)
            {
                Console.WriteLine("ERROR: Connection string is empty.");
                return;
            }

            NpgsqlConnection cnn = null;
            NpgsqlCommand cmd = null;
            NpgsqlDataReader reader = null;
            try
            {

                cnn = new NpgsqlConnection(CNN_STRING);

                cnn.Open();
                if (QUERY != null)
                {
                    cmd = new NpgsqlCommand(QUERY, cnn);
                    reader = cmd.ExecuteReader();
                    int columns = reader.FieldCount;

                    if (PATH == null)
                    {
                        while (reader.Read())
                        {
                            Console.Write(reader[0].ToString());
                            for (int i = 1; i < columns; i++)
                            {
                                Console.Write(COLS_DELIMITER + reader[i].ToString());
                            }

                            Console.Write(ROW_DELIMITER);
                        }
                    }
                    else
                    {
                        StreamWriter sw = new StreamWriter(PATH, false);

                        while (reader.Read())
                        {
                            sw.Write(reader[0].ToString());
                            for (int i = 1; i < columns; i++)
                            {
                                sw.Write(COLS_DELIMITER + reader[i].ToString());
                            }

                            sw.Write(ROW_DELIMITER);
                        }

                        sw.Flush();
                        sw.Close();
                    }

                    cmd.Dispose();
                }
                else
                    Console.WriteLine("Version: {0}", cnn.ServerVersion);
            }
            catch (Exception ex)
            {
                Console.WriteLine("ERROR: {0}", ex.Message);
            }
            finally
            {
                cnn.Close();
            }

        }
    }
}

Консольная программа - если PATH не задан, то выводит в консоль. Если QUERY не задан, то выводит версию PostgreSQL. COLS_DELIMITER - разделитель колонок, ROW_DELIMITER - разделитель строк. Код сам определяет количество колонок в запросе и выводит их все.

Далее, основываясь на этом коде, я создавал CSV файлы и с помощью SSIS закачивал их в SQL Server.

Как поменять область видимости переменной в BIDS (SSIS)

Если у кого то, как и у меня стоит английский BIDS, то под областью видимости я понимаю свойство Scope, которое поменять стандартными средствами невозможно. И я совершенно не понимаю почему!!!

Ну да ладно. Для своего пакета SSIS я создал порядка 10 переменных и потом заметил, что все имеют область видимости только в одном элементе, а нужны были во многих. Единственный путь поменять область видимости - это удалить переменные и создать заново, прям как работа над ошибками в школе ;)

Естественно этот путь меня не впечатлил, поэтому я открыл код пакета в текстовом редакторе. В очередной раз ужаснувшись увиденному, я понял, что этот путь тоже не лучший. Поэтому пошел в Google. И нагуглил инструмент под названием BIDS Helper. Детально я в нем не разбирался, судя из увиденного возможностей довольно много. Единственное, чего хотелось, скорее поменять область видимости, как это сделать написано тут. Область видимости поменял, правда после его установки при наведении на перемнную фокуса ввода перестало появляться окошко свойств, но я думаю это фича, а не баг.

Экспорт из MySQL в CSV файл

SELECT *
INTO OUTFILE 'products.csv'
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
ESCAPED BY '\\'
LINES TERMINATED BY '\n'
FROM products

На Windows файл появиться в папке текущей БД.

Импорт в MySQL из дамп-файла

$ mysql -u username -p -h localhost data-base-name < data.sql

Подробнее тут.

Работа с логами джобов в SQL Server

Для более удобного анализа истории выполнения джобов, например для анализа времени выполнения, стандартный лог не очень подходит. Но все данные, которые видны в истории выполнения джоба хранятся в таблице msdb..sysjobhistory. Вот ее структура:

CREATE TABLE dbo.sysjobhistory(
    instance_id int IDENTITY(1,1) NOT NULL,
    job_id uniqueidentifier NOT NULL,
    step_id int NOT NULL,
    step_name sysname NOT NULL,
    sql_message_id int NOT NULL,
    sql_severity int NOT NULL,
    message nvarchar(4000) NULL,
    run_status int NOT NULL,
    run_date int NOT NULL,
    run_time int NOT NULL,
    run_duration int NOT NULL,
    operator_id_emailed int NOT NULL,
    operator_id_netsent int NOT NULL,
    operator_id_paged int NOT NULL,
    retries_attempted int NOT NULL,
    server sysname NOT NULL
)

Тут есть описание этой таблицы.
job_id определяется по таблице  msdb..sysjobs, например вот так:

SELECT * FROM msdb..sysjobs WHERE name like '%job%'

Детально со всеми полями не разбирался, назначение большинства понятно из названий. Меня интересовало поле run_duration. Поле имеет тип int, там хранится время выполнения шага (строки, в которых step_id = 0, относятся ко всему джобу, т.е. в такой строке в поле run_duration будет время выполнения всего джоба). Формат: hhmmss, поскольку тип int, нули слева не отображаются. Т.е. значение 709 - это 7 минут 9 сек, 34508 - 3 часа 45 минут 8 секунд.
Для преобразования этого формата в секунды я написал функцию:

-- Конвертация времени из формата HHMMSS типа int в секунды

create function fn_convert_intdt_to_sec ( @ts int )
returns int
as
begin
    if (@ts < 60) return @ts
   
    declare @sec int = @ts - (@ts/100)*100
    declare @min int = (@ts - (@ts/10000)*10000)/100
    declare @hr int = @ts/10000
   
    return @hr*3600 + @min*60 + @sec

end

В принципе если в передаваемом значении часы будут содержать более 2-х знаков, то тоже отработает.