Главная | Обратная связь | Поможем написать вашу работу!

Создание хранимых процедур для заполнения базы данных

СОДЕРЖАНИЕ

1. Перечень отрабатываемых учебных вопросов и действий:

- 1-й учебный вопрос: разработка структуры корпоративной базы данных (БД) предприятия с использованием СУБД MSSQLServer; программирование хранимых процедур для заполнения БД данными, имитирующими деятельность предприятия за несколько лет; разработка структуры хранилища данных для созданной модели корпоративной базы данных.

- 2-й учебный вопрос: описание источников данных;потоков данных, порядка и условия их выполнения; правил преобразования данных; создание правил выполнения пакета по расписанию.

Методические рекомендации обучаемым по подготовке к лабораторной работе.

Рекомендуемое содержание:

- целевая установка: получение навыков работы по созданию базы корпоративных данных и хранимых процедур для их обработки, разработка хранилища данных для созданной модели БД.

- теоретические сведения: понятие, назначение OLAP-систем, схемы работы с ними;

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

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

- отчетность по занятию:электронный файл задания для самопроверки, текстовый документ в электронном виде с полученными результатами.

3. Перечень руководств и пособий, подлежащих изучению перед занятием: конспект лекцийпо теме «Основы OLAP-систем, хранилищ данных».

4.Приложения: приведенный далее текст с материаламираспечатки содержания лабораторной работы и состава заданий для студентов.

Й учебный вопрос: разработка структуры корпоративной базы данных (БД) предприятия с использованием СУБД MSSQLServer; программирование хранимых процедур для заполнения БД данными, имитирующими деятельность предприятия за несколько лет; разработка структуры хранилища данных для созданной модели корпоративной базы данных.

Принятие любого управленческого решения в процессе управления крупным предприятием, невозможно не обладая необходимой для этого аналитической информацией, получаемой в процессе сбора, отсеивания и предварительной обработки данных с целью предоставления результирующей информации пользователям для статистического анализа и создания аналитических отчетов. Поэтому, корпоративные информационные системы, как правило, содержат приложения, предназначенные для комплексного многомерного анализа данных, их динамики, тенденций и т.п., называемые системами поддержки принятия решений. Эти приложения основываются на концепции хранилищ данных (Datawarehouses).

Ральф Кимбалл (RalphKimball), один из авторов концепции хранилищ данных, описывал хранилище данных как "место, где люди могут получить доступ к своим данным". Он же сформулировал и основные требования к хранилищам данных:

· поддержка высокой скорости получения данных из хранилища;

· поддержка внутренней непротиворечивости данных;

· возможность получения и сравнения так называемых срезов данных (sliceanddice);

· наличие удобных утилит просмотра данных в хранилище;

· полнота и достоверность хранимых данных;

· поддержка качественного процесса пополнения данных.

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

Типичное хранилище данных, как правило, отличается от обычной реляционной базы данных. Во-первых, обычные базы данных предназначены для того, чтобы помочь пользователям выполнять повседневную работу, тогда как хранилища данных предназначены для принятия решений. Например, продажа товара и выписка счета производятся с использованием базы данных, предназначенной для обработки транзакций, а анализ динамики продаж за несколько лет, позволяющий спланировать работу с поставщиками, – с помощью хранилища данных.

Во-вторых, обычные базы данных подвержены постоянным изменениям в процессе работы пользователей, а хранилище данных относительно стабильно: данные в нем обычно обновляются согласно расписанию (например, еженедельно, ежедневно или ежечасно – в зависимости от потребностей). В идеале процесс пополнения представляет собой просто добавление новых данных за определенный период времени без изменения прежней информации, уже находящейся в хранилище.

И в-третьих, обычные базы данных чаще всего являются источником данных, попадающих в хранилище. Кроме того, хранилище может пополняться за счет внешних источников, например статистических отчетов.

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

Технология комплексного многомерного анализа данных получила название OLAP (On-LineAnalyticalProcessing). OLAP – это ключевой компонент организации хранилищ данных. Концепция OLAP была описана в 1993 году Эдгаром Коддом, известным исследователем баз данных и автором реляционной модели данных. В 1995 году на основе требований, изложенных Коддом, был сформулирован так называемый тест FASMI (FastAnalysisofSharedMultidimensionalInformation — быстрый анализ разделяемой многомерной информации), включающий следующие требования к приложениям для многомерного анализа:

предоставление пользователю результатов анализа за приемлемое время (обычно не более 5 с), пусть даже ценой менее детального анализа;

возможность осуществления любого логического и статистического анализа, характерного для данного приложения, и его сохранения в доступном для конечного пользователя виде;

многопользовательский доступ к данным с поддержкой соответствующих механизмов блокировок и средств авторизованного доступа;

многомерное концептуальное представление данных, включая полную поддержку для иерархий и множественных иерархий (это — ключевое требование OLAP);

возможность обращаться к любой нужной информации независимо от ее объема и места хранения.

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

Создание базы данных

Атрибуты колонок таблиц SQL - Server:

1. ColumnName – имя колонки

2. DataType – тип данных

3. Length – длина n для символьных видов

4. AllowNulls – разрешение значения NULL

5. Description – описание

6. DefaultValue – значение по умолчанию (н-ер: NewId() для uniqueidentifier, GetDate() для datetime)

7. Precision – точность (общее количество знаков), р – для decimal и numeric

8. Scale – масштаб (количество знаков после запятой), s – для decimal и numeric

9. Identity – признак счетчика

10. IdentitySeed – начальное значение счетчика

11. IdentityIncrement – шаг приращения счетчика

12. IsRowGuid – признак глобального идентификатора

13. Formula – для вычисляемых столбцов (н-ер: Цена * Количество)

14. Collation – сопоставление для сравнения и сортировки строк

Типы данных SQL-Server:

Вид	Тип	Интервал значений	Размер
Двоичные	binary (n) varbinary (n) image	до 8 000 байт до 8 000 байт до 2 Гбайт	n n
Символьные	char (n) varchar (n) nchar (n) nvarchar (n)	до 8 000 байт до 8 000 байт до 4 000 байт (Unicode) до 4 000 байт (Unicode)	n n n n
Текст	text n text	до 2 Гбайт до 1 Гбайт (Unicode)
Дата и время	datetimesmalldatetime	01.01.1753-31.12.9999г. до 3,33 мс 01.01.1900-06.06.2079г. до 1 мин.	8 байт 4байта
Точное представление чисел	decimal (p, s) numeric (p, s)	p <= 38, s <= p p <= 38, s <= p	При p = 2 – 2 байта, при p = 38 – 17 байт
Числа с плавающей точкой	float (n) real	± 1,810³⁰⁸ ± 1,810³⁰⁸	n = 1 – 53 n = 24 n – число бит мантиссы
Целочисленные типы	intsmallint tinyint b i gi nt	± 210¹⁰ ± 32 767 0 – 255 ±910¹⁹	4байта 2байта 1байт 8 байт
Денежные типы	moneysmallmoney	± 9*10¹⁵, 4 знака после запятой ± 214 748.3648	8 байт 4байта
Специальные	bit timestamp uniqueidentifier sysname sql_variant table cursor	0 или 1 (логический) отслежив.последоват.изм. записей NewID()	1 бит 8 байт 16 байт

Установка формата даты: SETDATEFORMATdmy. Первого дня недели: SETDATEFIST 1 (1 – понедельник, 7 – воскресенье).

Преобразование типов данных:

CAST(exprAStype)

CONVERT(type, expr, [style]) style – при преобразовании в дату: 4 – dd.mm.yy, 104 – dd.mm.yyyy

Структура примера исходной базы данных КИС:

Создание хранимых процедур для заполнения базы данных

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

CREATE FUNCTION ПервыйДеньМесяцаПоДате (@ДатаSmallDateTime)

RETURNS SmallDateTime

BEGIN

Select @Дата = @Дата - Day(@Дата) + 1

return @Дата

END

CREATE FUNCTION ПроверкаТабельногоНомера (@Код char(4))

RETURNS bit

BEGIN

Declare @Yes bit

if exists(select ТабельныйНомер from Сотрудники where ТабельныйНомер = @Код)

Set @Yes = 1

else

Set @Yes = 0

return @Yes

END

CREATE PROCEDURE dbo.НачислениеЗП (@МесяцSmallDateTime)

insert into dbo.Зарплата

select NewId() as Код,

@Месяц as Месяц,

dbo.Сотрудники.ТН,

dbo.Должности.Окладas Начислено,

dbo.Должности.Оклад*0.13 as Удержано

from

dbo.Сотрудникиinnerjoindbo.Должности

ondbo.Сотрудники.Должность = dbo.Должности.Код

CREATE PROCEDURE dbo.MassCalculate (@НачДатаSmallDateTime)

declare @ДатаSmallDateTime

declare @iTinyInt

set @i = 1

set @Дата = Convert(SmalldateTime, '01 ' + DateName(Month, @НачДата)

+ ' ' + Cast(DatePart(Year, @НачДата) as Char(4)), 107)

while @i< 120

begin

select @Дата = DateAdd(Month, 1, @Дата)

execdbo.НачислениеЗП @Дата

select @i = @i + 1

end

CREATE PROCEDURE dbo.Продажи10лет (@ДатаНачSmallDateTime) AS

Declare @iМесяцInt, @iСотрInt, @iТовInt

Declare @СотрВсегоInt, @СотрInt

Declare @ТНСотрChar(5)

Declare @ТовВсегоInt, @ТовInt, @КолТовInt

Declare @КодТовараChar(5)

Declare @ЦенаТовараSmallMoney

Declare @iПродажInt, @КолПродажInt

Declare КурсорСотр Cursor local scroll for Select ТН fromСотрудники

Declare КурсорТовары Cursor local scroll for Select Код, Цена from Товары

Open КурсорСотр

Open КурсорТовары

Select @СотрВсего = Count(Сотрудники.ТН) From Сотрудники

Select @ТовВсего = Count(Товары.Код) From Товары

Delete from Продажи

Select @ДатаНач = ПервыйДеньМесяцаПоДате(@ДатаНач)

Set @iМесяц = 1

While @iМесяц<= 120

begin

Set @iСотр = 1

While @iСотр<= @СотрВсего

begin

Select @Сотр = Round(Rand() * @СотрВсего, 0)

Fetch Absolute @Сотр fromКурсорСотр Into @ТНСотр

Set @iПродаж = 1

Select @КолПродаж = Floor(Rand() * 50)

While @iПродаж<= @КолПродаж

begin

Select @КолТов = Floor(Rand() * 5)

Select @Тов = Round(Rand() * @ТовВсего, 0)

Fetch Absolute @Тов fromКурсорТовары Into @КодТовара,@ЦенаТовара

InsertInto Продажи (Код,Дата,Сотрудник,Товар, Количество,Сумма) Values

(NewId(),@ДатаНач,@ТНСотр,@КодТовара,@КолТов,@ЦенаТовара*@КолТов)

Select @iПродаж = @iПродаж + 1

end

Set @iСотр = @iСотр + 1

end

Select @ДатаНач = DateAdd(Month, -1, @ДатаНач)

Select @iМесяц = @iМесяц + 1

end

Close КурсорСотр

Close КурсорТовары

DeallocateКурсорСотр

DeallocateКурсорТовары

12 3 4 5 Следующая ⇒

Воспользуйтесь поиском по сайту: