axforum blogs: Геоданные в CRM и SQL Server Reporting Services (часть 1)

[Blog bot]

Источник: http://axforum.info/forums/blog.php?b=267
==============

Недавно мне пришлось столкнуться с еще одним интересным способом представления данных CRM - в виде отчетов-карт. Опыт был довольно интересный, поэтому спешу с вами им поделиться.

Первая из проблем с которыми нам предстоит столкнуться - это задача поиска самих карт для будущих отчетов. К сожалению, в базовой поставке (даже локализованной версии SQL Server) идут только карты США. Эти карты находятся в кталоге C:\Program Files (x86)\Microsoft Visual Studio 9.0\Common7\IDE\PrivateAssemblies\MapGallery
в виде обычных RDL файлов с "преднастроенными" элементами - картами. Сюда же вы сможете добавлять свои отчеты-шаблоны с картами, сразу же после того как научитесь их создавать.

Кроме готовой галереи, Reporting Services может использовать карты в формате Shape (*.shp). Из всего бесплатного что мне удалось найти в интернете могу посоветовать только следующий сайт: http://www.gadm.org/country. Загрузим отсюда карту РФ и можем приступать.



С галереей и шейп-картами мы разобрались, но что такое пространственный (spatital) запрос SQL на скриншоте выше? Немного теории...

Геоданные (или пространственные типы данных) появились в SQL Server 2008 как новые типы данных География (geography) и Геометрия (geometry), а так же в соответствующих им CLR типах. Поддержка отображения новых типов так же была добавлена в Managerment Studio, в классы сборок SDK и, конечно же, в Reporting Services. Итак, география и геометрия, по сути, представляют собой те же яйца, только в анфаз и в профиль. Оба типа используются для описания формы пространственных объектов и полностью совместимы по типам, однако используют различные системы координат. Геометрия описывает фигуры на плоскости - и оперирует евклидовой системой координат, в то время как География описывает их положение в на сфере земли - в эллиптической системе. В случае с географией так же вводится понятие идентификатора системы координат (подвида системы небесных координат), которая будет использоваться при расчетах. Геометрия, очевидно, это некоторая проекция географического объекта на эллипсе земли на плоскость карты (видов проекций так же несколько). Если не хочется сильно ломать мозг, то все отличия, по сути заключаются в том как вы будете задавать координаты точки: (X,Y) или (Широта, Долгота), и как, соответственно, над этими координатами будут выполняться математические операции, например, вычисление расстояния между точками. На плоскости это будет длинна линии соединяющей точки, на эллипсе - длинна дуги. В нашем, сугубо практическом случае, когда цель - не исследовать движение небесных светил, а произвести аналитику продаж в разрезе географии, нас мало будут интересовать все изложенные выше тонкости - они были приведены лишь для информации.

Итак, мы имеем выбор: хранить геоданные в теле отчета (при подключении карты), или выбирать их из базы, как и все прочие данные отчета. Второй способ на мой взгляд более правильный, так как он позволяет:

1. строить более гибкие запросы пространственных данных
2. повторно использовать геоданные в разных отчетах
3. уменьшает размер файла отчета

К счастью, существует замечательная утилита, которая позволяет загрузить данные из Шейп-карты в таблицу SQL Server. Скачать ее можно отсюда: http://www.sharpgis.net/page/Shape2SQL.aspx

Далее запускаем приложение и указываем базу данных в которой мы хотим создать таблицу геоданных.



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



RUS_adm1.shp - карта России в виде цельного полигона. Вполне годится чтобы увидеть мощь нашей державы.

RUS_adm2.shp - карта России в виде субъектов Российской Федерации по данным на 2009 год. С тех пор Пермская область и Коми-пермяцкий автономный округ успели объединиться, но в целом карта вполне ничего. Особенно хорошо, что регионы на карте имеют правильные федеральные номера. Названия регионов тоже совершенно правильные, но, к сожалению, существует проблема с кодировками, так что при импорте эти данные будут утрачены.

RUS_adm3.shp - карта России разорванная на самые мелкие составляющие. Годится чтобы увидеть состав областей регионов.

Теперь, когда данные успешно загружены мы можем сделать свой пространственный запрос и увидеть родину прямо в SQL Server Management Studio:
Код:
select * from RUS_adm2


Итак, мы имеем типы данных, которые описывают форму географических объектов и методы работы с ними. Как эти типы используются в Reporting Services? SSRS для отображения пространственных типов использует элемент отчета - Карта (map). Карта представляет собой набор слоев, которые могут накладываться друг на друга. Для наполнения содержимым, по умолчанию, используются 2 набора данных: первый возвращает пространственные данные, второй - аналитические. Слой, по сути, это метод связывания, обработки и отображения данных из двух этих источников. Например, пространственный запрос возвращает полигоны регионов Российской федерации, а аналитический - суммарные продажи в регионе. Тогда слой может, например, отображать красным регионы где продажи существенно ниже плановых, зеленым - регионы, где план выполнен и желтым - регионы, где продажи несущественно отклонились от планируемого значения.

Теперь самое время попытаться использовать полученные знания в отчете. Первое что мы сделаем - это добавим объекту Территория новое числовое поле: код региона. Мы будем использовать его, чтобы связать аналитические данные с пространственными.



Теперь вернемся в SQL Server Management Studio и подготовим наши запросы. Для простоты геоданные будем выбирать отдельно, аналитические данные отдельно:
Код:
-- геоданныеselect GEO.ID as Region ,T.name as Name ,GEO.geom as Geofrom RUS_adm1 GEOleft outer join FilteredTerritory Ton GEO.ID = T.new_regioncode-- аналитические данныеselect CRMAF_TR.new_regioncode as Region ,SUM(CRMAF_OP.actualvalue) as Totalfrom FilteredOpportunity CRMAF_OPinner join FilteredAccount CRMAF_ACon CRMAF_OP.accountid = CRMAF_AC.accountidinner join FilteredTerritory CRMAF_TRon CRMAF_AC.territoryid = CRMAF_TR.territoryidgroup by CRMAF_TR.new_regioncode
Теперь откроем Business Intelligence Developer Studio и создадим новый проект отчетов и новый пустой отчет.

Создадим необходимый нам источник данных (DataSource) с названием "CRMDataSource"



Создадим два набора данных (DataSet) с названиями "GeoSet" и "SalesSet" для пространственного и аналитического запроса, соответственно.





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



Выберем в качестве источника пространственный запрос. На следующем шаге мастера выберем наш пространственный набор данных GeoSet и настроим позиционирование карты на следующем шаге.



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

1. Базовая карта. Просто контурная карта и все. Данный пресет удобен, если мы хотим нанести аналитику или другие геоданные (расположение офисов) на следующий слой карты.
2. Цветная аналитическая карта. Аналитические данные визуализируются за счет раскраски полигонов геоданных.
3. Пузырьковая карта. Аналитические данные визуализируются при помощи пузырьков разного размера.

Выберем цветную аналитическую карту и перейдем к следующему шагу.

Продолжение статьи смотрите в посте Геоданные в CRM и SQL Server Reporting Services (часть 2).


Источник: http://axforum.info/forums/blog.php?b=267